SOLVED
Home

Custom value if a value is found using vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-294024%22%20slang%3D%22en-US%22%3ECustom%20value%20if%20a%20value%20is%20found%20using%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294024%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20we%20put%20a%20custom%20value%20like%20Found%2C%20if%20we%20find%20a%20match%20while%20doing%20a%20vlookup.%20Like%20if%20you%20iferror%2C%20we%20can%20replace%20N%2FA%20with%20a%20custom%20value.%20Similarly%20can%20we%20put%20a%20custom%20text%2Fvalue%20is%20a%20vlookup%20evaluates%20to%20True%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-294024%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Evlookup%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294996%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20value%20if%20a%20value%20is%20found%20using%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294996%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20thanks%20for%20the%20explanation.%20I%20got%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294697%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20value%20if%20a%20value%20is%20found%20using%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F247930%22%20target%3D%22_blank%22%3E%40Sam55%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D%3CSTRONG%3EIF%3C%2FSTRONG%3E(%3CSTRONG%3EIFERROR%3C%2FSTRONG%3E(%3C%2FSPAN%3E%3CSTRONG%3EVLOOKUP()%3C%2FSTRONG%3E%3CSPAN%3E%2C%22%3CU%3ENO%3C%2FU%3E%22)%26lt%3B%26gt%3B%22%3CU%3ENO%3C%2FU%3E%22%2C%22%3CU%3EFound%3C%2FU%3E%22%2C%22%3CU%3ENot%20Found%3C%2FU%3E%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStarting%20off%20with%20the%20VLOOKUP%3A%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3EIFERROR%3C%2FSTRONG%3E(%3C%2FSPAN%3E%3CSTRONG%3EVLOOKUP()%3C%2FSTRONG%3E%3CSPAN%3E%2C%22%3CU%3ENO%3C%2FU%3E%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhen%20a%26nbsp%3BVLOOKUP%20can't%20find%20a%20match%20it%20yields%20the%20result%20as%20%22%23N%2FA%22%2C%20which%20is%20an%20error%20code.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20IFERROR%20changes%20that%20no%20match%20error%20code%20value%20into%20the%20string%20%22NO%22%2C%20meaning%20a%20match%20wasn't%20found.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESecond%3A%20IF%20formula%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20IF%20formula%20is%20then%20determining%20whether%20the%26nbsp%3BVLOOKUP%20resulted%26nbsp%3Bin%20a%20no%20match%20which%20the%20IFERROR%20would%20then%20change%20the%20output%20to%20%22NO%22.%20The%20first%20condition%20is%20stating%20if%20the%20IFERROR(VLOOKUP())%20function%20does%20not%20equal%20(%22%26lt%3B%26gt%3B%22)%20the%20value%20of%20%22NO%22%20then%20a%20match%20must%20have%20been%20found%20which%20is%20the%20second%20statement%20of%20the%20IF%20formula%20which%20would%20be%20the%20output%20of%20%22FOUND%22.%20The%20last%20statement%20then%20would%20be%20a%20false%20result%2C%20or%20if%20the%20IFERROR(VLOOKUP())%20resulted%20in%20%22NO%22%20then%20the%20output%20would%20be%20%22NOT%20FOUND%22.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20hope%20this%20clarifies%20the%20formula%20at%20least%20a%20little%20bit%20for%20you.%20Let%20me%20know%20if%20it%20makes%20sense.%20Thanks!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294444%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20value%20if%20a%20value%20is%20found%20using%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294444%22%20slang%3D%22en-US%22%3E%3CP%3EThanks.%20Yes%20I%20tried%20this%20and%20it%20works.%20However%20what%20is%20the%20logic%20behind%20this%20formula%3F%3C%2FP%3E%3CP%3EMeaning%20how%20can%20we%20break%20down%20this%20formula%20for%20understanding%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294146%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20value%20if%20a%20value%20is%20found%20using%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294146%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20suggest%20MATCH()%20or%20COUNTIF().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294129%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20value%20if%20a%20value%20is%20found%20using%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294129%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F247930%22%20target%3D%22_blank%22%3E%40Sam55%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20this%20is%20possible.%20See%20the%20example%20below%20for%20reference.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(IFERROR(%3CSTRONG%3EVLOOKUP()%3C%2FSTRONG%3E%2C%22NO%22)%26lt%3B%26gt%3B%22NO%22%2C%22Found%22%2C%22Not%20Found%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20733px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F61189i9AE8ADED83CDA44E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot_1.png%22%20title%3D%22Screenshot_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20drop%20your%20vlookup%20function%20into%20this%20formula%20and%20it%20will%20be%20able%20to%20tell%20you%20whether%20or%20not%26nbsp%3Bthe%20word(s)%20you%20are%20looking%20for%26nbsp%3Bhas%20been%20found%20or%20not.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Sam55
Occasional Contributor

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

Hello @Sam55,

 

Yes, this is possible. See the example below for reference. 

 

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

 

 Screenshot_1.png

 

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. 

 

Hi

 

I would suggest MATCH() or COUNTIF().

 

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?

Solution

@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!

Yes thanks for the explanation. I got it.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies