CODEWITHSUNDEEP
excelexcel-formula
Brad
Brad

Reputation: 1480

Avoiding index & match #N/A value

I am indexing column O on a match of a value with a combined name within column L.

If the combined name is not found within the table I am index/matching, I will be passed back a #N/A value. How could I avoid this? I've looked into the ISNA function however it only passes back a True or False value. I could use this to make another formula to set it, but am trying to find a shorter quicker way.

The current index/match being used is:

=INDEX(O:O,MATCH(ADIST& " " &APROD,L:L,0))

And if I am being passed back a #N/A value I'd like it to say "No Value".

Upvotes: 4

Views: 3296

Answers (2)

Jordan
Jordan

Reputation: 4514

Use IFERROR to set an alternative value if the formula you are using returns an error:

=IFERROR(INDEX(O:O,MATCH(ADIST& " " &APROD,L:L,0)),"No Value")

Upvotes: 5

R3uK
R3uK

Reputation: 14537

Use IFNA or IFERROR functions, same syntax but IFNA is more accurate on the type of error :

=IFNA(expression, value if expression returns an error)

so for you :

=IFNA(INDEX(O:O,MATCH(ADIST& " " &APROD,L:L,0)),"No Value")

Upvotes: 2

Related Questions