Eliminating n/a from VLOOKUP's

[JTL9161]

I have 2 VLOOKUP's combined in the formula below. Trying to add to it to eliminate the #N/A error. Maybe I need an INDEX or MATCH but not sure on how to write it.

=E2&" ("&VLOOKUP(E2,$K$2:$L$33,2,FALSE)&" "&VLOOKUP(E2,$K$2:$M$33,3,FALSE)&")"




Thank you,
James

 

[Joe4]

Wrap each VLOOKUP function piece of your formula with an IFNA function, where you tell it what to return in the event of a non-match.
See: IFNA Function

 

[JTL9161]

thanks, I'll try it

 

[Cubist]

If you're using the latest version of Excel, I recommend using XLOOKUP instead of older lookup functions. XLOOKUP has a built-in parameter called [if_not_found] that lets you specify what to return if no match is found.

This is better than using IFNA, which hides all #N/A errors, including those already present in your data—not just the ones caused by the lookup.

 

[Joe4]

If you're using the latest version of Excel, I recommend using XLOOKUP instead of older lookup functions. XLOOKUP has a built-in parameter called [if_not_found] that lets you specify what to return if no match is found.

This is better than using IFNA, which hides all #N/A errors, including those already present in your data—not just the ones caused by the lookup.

I forgot about that cool option in the XLOOKUP function!