Vlookup that does not generate #N/A

[contra76]

Hi all,

Vlookup code I'm using:

Please Login or Register  to view this content.

Column A has abbreviated names.

Mapping table has abbreviated names and full names.

If name does not exist, vlookup writes "#N/A."

I would like it to just copy the abbreviated name instead if name is not found.

On a tight deadline, please help?
Thank you!

[daddylonglegs]

Try using IFERROR function, i.e.

=IFERROR(VLOOKUP(A2,[MappingTables.xlsx]names!A:B,2,0),A2)

[scottylad2]

Hi all,

Vlookup code I'm using:

Please Login or Register  to view this content.

Column A has abbreviated names.

Mapping table has abbreviated names and full names.

If name does not exist, vlookup writes "#N/A."

I would like it to just copy the abbreviated name instead if name is not found.

On a tight deadline, please help?
Thank you!

Do the abreviated names have parts of the real name? IE looking up edinburgh in a2, =VLOOKUP(a2,[MappingTables.xlsx]names!A:B,2,)

where A2 contains "*Edin*" and that works. but you would get a problem if there was partial names that were duplicated, edinburgh and edinside....

[contra76]

Try using IFERROR function, i.e.

=IFERROR(VLOOKUP(A2,[MappingTables.xlsx]names!A:B,2,0),A2)

you guys amaze me time and time again. Thank you both for the reply. This works like a charm!

[contra76]

I don't know why but this worked the other day and now it's populating "0s" instead of a blank. Remedy?

[NBVC]

Do you mean if A2 is blank, you want a blank instead of a 0?

If so,


=IF(A2="","",IFERROR(VLOOKUP(A2,[MappingTables.xlsx]names!A:B,2,0),A2))