Dec 03 2018 03:24 AM
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?
Dec 03 2018 07:42 AM
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.
Dec 03 2018 08:25 AM
Hi
I would suggest MATCH() or COUNTIF().
Dec 03 2018 05:32 PM
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?
Dec 04 2018 07:54 AM
Solution
=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!
Dec 04 2018 03:23 PM
Yes thanks for the explanation. I got it.
Dec 04 2018 07:54 AM
Solution
=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!