Have forgotten how to do a vlookup or a match to return values

[buteaur]

i have one column with numbers and another column with numbers and names associated. I cannot for the life of me remember how to compare the two columns of numbers and when there is a match, return the name associated with any matches.

A2:A496 has numbers
H2:H6494 has numbers where a match needs to be found. I2:I6494 has the names associated with the numbers in H.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mohadin]

=iferror(Vlookup($A$2:$A$496,$H$2:$I$6494,2,0),"")

=IFERROR(INDEX($H$2:$I$6494,MATCH($A$2:$A$6496,$H$2:$H$6494,0),2),"")

 

[Peter_SSs]

the best solution often varies by version.

For example, for this problem, if you have the XLOOKUP function then the following formula placed in a single cell will return all 495 results without having to copy the formula down 495 rows.

=XLOOKUP(A2:A496,H2:H6494,I2:I6494,"Not found")

 

[buteaur]

=iferror(Vlookup($A$2:$A$496,$H$2:$I$6494,2,0),"")

=IFERROR(INDEX($H$2:$I$6494,MATCH($A$2:$A$6496,$H$2:$H$6494,0),2),"")

Thanks!

 

[buteaur]

For example, for this problem, if you have the XLOOKUP function then the following formula placed in a single cell will return all 495 results without having to copy the formula down 495 rows.

=XLOOKUP(A2:A496,H2:H6494,I2:I6494,"Not found")

Thank you!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

.. but please also follow-up on post #2!
If you want people to help in the future, you should make it as easy as possible for them by giving them the information they need to best answer your questions.

 

[mohadin]

You are welcome.
Thanks for the feedback
Be happy and safe