Another Excel Query

VoucherMan

victor2 wrote: »

You could wrap an IF statement around it. Gets a bit messy, but it would look something like this:
=IF(ISNA(VLOOKUP($A$13,G1:H14,2,FALSE)),"No Match",VLOOKUP($A$13,G1:H14,2,FALSE))

Not sure if the op's sorted the problem yet but since I've been watching all these Excel queries.

I recently had a similar issue. Looking in another workbook sometimes returned N/A when using
=VLOOKUP(G$1,sizes.xlsx!sets,2,FALSE)

My best solution so far (which I saw mentioned recently on this forum ) to add IFERROR followed by the text of your choice.

=IFERROR(VLOOKUP(G$1,sizes.xlsx!sets,2,FALSE),"text")

Unfortunatly I've not been able to use it at work since we're still using '2003 but, as has already been mentioned the op seems to be using a later version.

victor2

VoucherMan wrote: »

Not sure if the op's sorted the problem yet but since I've been watching all these Excel queries.

I recently had a similar issue. Looking in another workbook sometimes returned N/A when using
=VLOOKUP(G$1,sizes.xlsx!sets,2,FALSE)

My best solution so far (which I saw mentioned recently on this forum ) to add IFERROR followed by the text of your choice.

=IFERROR(VLOOKUP(G$1,sizes.xlsx!sets,2,FALSE),"text")

Unfortunatly I've not been able to use it at work since we're still using '2003 but, as has already been mentioned the op seems to be using a later version.

That is elegant and works! Nice one.

stevemcol

I've had excel return #na when vlookup points at empty cells. Not a very elegant solution but I've pre-populated empty cells with a . (dot)
Didn't get as far as play with iferror but may next time I come across the same problem again.

Stompa

Another option is to use conditional formatting to hide the error:

http://support.microsoft.com/kb/182189