 • 541K Members
• 3,932 Online
• 645K Conversations
SOLVED

Highlighted

# 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?

5 Replies
Highlighted

# Re: Custom value if a value is found using vlookup

Hello @Sam55,

Yes, this is possible. See the example below for reference. 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.

Highlighted

# Re: Custom value if a value is found using vlookup

Hi

I would suggest MATCH() or COUNTIF().

Highlighted

# Re: Custom value if a value is found using vlookup

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?

Highlighted
Solution

# Re: Custom value if a value is found using vlookup

@Sam55

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!

Highlighted

# Re: Custom value if a value is found using vlookup

Yes thanks for the explanation. I got it.

Related Conversations
pull data based on dropdown list
bbombb in Excel on
1 Replies
Custom profile: hide known file extensions
PatrickF11 in Microsoft Intune on
1 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies
Vlookup formula - partial text search
fredericomarquez in Excel on
2 Replies