1

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?

Improve this question
0

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.)

Improve this answer
1
  • 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. – marikamitsos Feb 21 '20 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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