I have a column of form response numbers, and on a separate sheet a column of a random (but sorted) subset of those response numbers with another column next to it that has some scores.

I need to get the scores by looking up corresponding response numbers and returning blanks when it doesn't find an exact match.

I tried using LOOKUP, but

"If search_key is not found, the item used in the lookup will be the value that’s immediately smaller in the range provided. For example, if the data set contains the numbers 1, 3, 5 and search_key is 2, then 1 will be used for the lookup."

From here: https://support.google.com/docs/answer/3256570?hl=en

Seems like a horribly unintuitive default. Anyway, no use complaining.

Any ideas?

1 Answer 1


I believe what you are looking for is the [VLOOKUP function instead.

=ArrayFormula(IFNA(VLOOKUP(A2:A11,Sheet2!$C$2:$D$7,2,0),"No match"))

enter image description here

Functions used:

(If you still face issues, please read how to and share a test sheet so as you can be easier helped.)

  • Have you tried the solution given? Did it work for you? If not we could try finding out why. If yes, you could accept it and even upvote it so others can benefit as well. Commented Feb 21, 2020 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.