SOLVED

How to make VLOOKUP return multiple error values

%3CLINGO-SUB%20id%3D%22lingo-sub-2759834%22%20slang%3D%22en-US%22%3EHow%20to%20make%20VLOOKUP%20return%20multiple%20error%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2759834%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20if%20this%20is%20possible%2C%20but%20what%20I%20am%20trying%20to%20do%20is%20a%20regular%20VLOOKUP%20with%20the%20caveat%20that%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EWhere%20the%20lookup%20value%20is%20an%20empty%20cell%2C%20instead%20of%20N%2FA%2C%20the%20formula%20returns%20%22Unknown%22%3C%2FLI%3E%3CLI%3EWhere%20the%20lookup%20value%20is%20missing%20from%20the%20table%20array%2C%20but%20is%20NOT%20an%20empty%20cell%2C%20the%20formula%20returns%20N%2FA%20(as%20usual.)%3C%2FLI%3E%3C%2FUL%3E%3CP%3ESo%20far%20I%20have%20only%20been%20able%20to%20use%20an%20IFERROR%20to%20pull%20back%20%22Unknown%22%20for%20all%20missing%20values%20in%20the%20table%20array.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20even%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2759834%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2759879%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20VLOOKUP%20return%20multiple%20error%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2759879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142435%22%20target%3D%22_blank%22%3E%40LL1991%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(A2%2C%20%24F%242%3A%24G%24100%2C%202%2C%20FALSE)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20this%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(A2%3D%22%22%2C%20%22Unknown%22%2C%20VLOOKUP(A2%2C%20%24F%242%3A%24G%24100%2C%202%2C%20FALSE))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2759972%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20VLOOKUP%20return%20multiple%20error%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2759972%22%20slang%3D%22en-US%22%3EThanks!!%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all

 

Not sure if this is possible, but what I am trying to do is a regular VLOOKUP with the caveat that:

 

  • Where the lookup value is an empty cell, instead of N/A, the formula returns "Unknown"
  • Where the lookup value is missing from the table array, but is NOT an empty cell, the formula returns N/A (as usual.)

So far I have only been able to use an IFERROR to pull back "Unknown" for all missing values in the table array. 

 

Is this even possible?

 

Many thanks

 

 

2 Replies
best response confirmed by LL1991 (Occasional Contributor)
Solution

@LL1991 

Let's say you have

 

=VLOOKUP(A2, $F$2:$G$100, 2, FALSE)

 

Change this to

 

=IF(A2="", "Unknown", VLOOKUP(A2, $F$2:$G$100, 2, FALSE))