Forum Discussion
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 evaluates to True?
=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!
Hello Sam55,
Yes, this is possible. See the example below for reference.
=IF(IFERROR(VLOOKUP(),"NO")<>"NO","Found","Not Found")
Just drop your vlookup function into this formula and it will be able to tell you whether or not the word(s) you are looking for has been found or not.
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?
=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!
Hi
I would suggest MATCH() or COUNTIF().
