I tried many formulas and either I get #VALUE or #N/A,
someone please help.


=VLOOKUP(A1,Sheet2!A15:B37,2,0)

I have in Sheet 2

cell A15 | B15

Australia | 61
Canada | 1
China | 86

Depending on whether A1 contains the:
• number
=INDEX(Sheet2!A15:A37,MATCH(A1,Sheet2!B15:B37,0))
• name
=INDEX(Sheet2!B15:B37,MATCH(A1,Sheet2!A15:A37,0))

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks for your quick reply.

I got it working with
=VLOOKUP(A1,Sheet2!A15:B37,2)

The formula works fine, except when the lookup cell is
empty, I get #N/A. I believe it can show an empty cell
with ISBLANK...what should be the code?
I tried =IF(ISBLANK(=VLOOKUP(A1,Sheet2!A15:B37,2)) but
doesn't work.

Try =IFERROR(VLOOKUP(A1,Sheet2!A15:B37,2),"")
Be aware that INDEX/MATCH combination is much more flexible ( and faster on large ranges) than VLOOKUP

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

=IFERROR.....didn't work.

I tried the Index/Match combination
=INDEX(Sheet2!B15:B37(MATCH(A1,Sheet2!A15:A37,0))

it may work...but I am getting an
error message saying "Your formula is missing a parenthesis--) or (.

Problem solved !

here's the formula :

=IFERROR(INDEX(Sheet2!$B$15:B44,1*(MATCH(Sheet2!A1,Labels!$A$15:$A$37,0))),"")

1* was missing from your formula.

Thank you so much

You don't need 1* but you did need the comma in front of MATCH.

Awesome!
Removed 1*

Thanks again.