Forum Discussion

Copper Contributor

Dec 03, 2018

Solved

Custom value if a value is found using vlookup

Can we put a custom value like Found, if we find a match while doing a vlookup. Like if you iferror, we can replace N/A with a custom value. Similarly can we put a custom text/value is a vlookup eval...

vlookup

Chris-RessConsulting
Dec 04, 2018
Sam55

=IF(IFERROR(VLOOKUP(),"NO")<>"NO","Found","Not Found")

Starting off with the VLOOKUP:
IFERROR(VLOOKUP(),"NO")

When a VLOOKUP can't find a match it yields the result as "#N/A", which is an error code.
The IFERROR changes that no match error code value into the string "NO", meaning a match wasn't found.

Second: IF formula
The IF formula is then determining whether the VLOOKUP resulted in a no match which the IFERROR would then change the output to "NO". The first condition is stating if the IFERROR(VLOOKUP()) function does not equal ("<>") the value of "NO" then a match must have been found which is the second statement of the IF formula which would be the output of "FOUND". The last statement then would be a false result, or if the IFERROR(VLOOKUP()) resulted in "NO" then the output would be "NOT FOUND".

I hope this clarifies the formula at least a little bit for you. Let me know if it makes sense. Thanks!

Sam55

Copper Contributor

Dec 04, 2018

Thanks. Yes I tried this and it works. However what is the logic behind this formula?

Meaning how can we break down this formula for understanding?

Chris-RessConsulting

Brass Contributor

Dec 04, 2018

Sam55

=IF(IFERROR(VLOOKUP(),"NO")<>"NO","Found","Not Found")

Starting off with the VLOOKUP:

IFERROR(VLOOKUP(),"NO")

When a VLOOKUP can't find a match it yields the result as "#N/A", which is an error code.

The IFERROR changes that no match error code value into the string "NO", meaning a match wasn't found.

Second: IF formula

The IF formula is then determining whether the VLOOKUP resulted in a no match which the IFERROR would then change the output to "NO". The first condition is stating if the IFERROR(VLOOKUP()) function does not equal ("<>") the value of "NO" then a match must have been found which is the second statement of the IF formula which would be the output of "FOUND". The last statement then would be a false result, or if the IFERROR(VLOOKUP()) resulted in "NO" then the output would be "NOT FOUND".

I hope this clarifies the formula at least a little bit for you. Let me know if it makes sense. Thanks!

Sam55
Copper Contributor
Dec 04, 2018
Yes thanks for the explanation. I got it.