I have this google sheet :

enter image description here

link is here :


On the top is my matrix table. It has key on the left and data on the right. I want to pull the key based on the data if the data is not null.

On the 2nd table i used manual copy/paste and it works perfectly. The formula in B10 :

=ifna( vlookup(B2,{B$2:B$6,$A$2:$A$6},2,0) ,)

But on the 3rd table i tried arrayformula , and the output is wrong. Can someone spot what is the problem with the formula in B17 :

=ARRAYFORMULA(ifna( vlookup(B2:M6,{B$2:B$6,$A$2:$A$6},2,0) ,) )
  • Your bottom formula is telling sheets to try to find every cell value in the range B2:M6 within ONLY the single-column range B2:B6. It seems you need to spend some further time studying the basics of how VLOOKUP works.
    – Erik Tyler
    Commented Jul 15, 2021 at 12:39

1 Answer 1


To be honest, I don't understand your underlying logic in choosing VLOOKUP for this task; and then, as I noted in the comment, it doesn't seem you yet understand the basics of how VLOOKUP even works.

That said, you don't need VLOOKUP at all. Try this simple formula:


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.