SOLVED

Checking multiple cells, skipping if blank and returning the value of the cell once it is found

%3CLINGO-SUB%20id%3D%22lingo-sub-2268127%22%20slang%3D%22en-US%22%3EChecking%20multiple%20cells%2C%20skipping%20if%20blank%20and%20returning%20the%20value%20of%20the%20cell%20once%20it%20is%20found%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2268127%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20tried%20all%20sorts%20of%20fomula's%20and%20the%20current%20version%20that%20I%20have%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(LEN(VLOOKUP(A2%2C%20inventory!A%3AE%2C%203%2C%20FALSE))%3D0%2CVLOOKUP(A2%2C%20inventory!A%3AE%2C%203%2C%20FALSE)%2C%26nbsp%3BVLOOKUP(A2%2C%20inventory!A%3AE%2C%204%2C%20FALSE))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20trying%20to%20do%2C%20is%20I%20have%202%20worksheets%20in%20a%20workbook%3A%3C%2FP%3E%3CP%3Elist%20and%20inventory%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorksheet%201%3A%26nbsp%3B%20List%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EFood%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EHealthy%20Desserts%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EUnhealthy%20Desserts%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EApple%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EChocolate%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3DIF(LEN(VLOOKUP(A3%2C%20inventory!A%3AE%2C%203%2C%20FALSE))%3D0%2CVLOOKUP(A3%2C%20inventory!A%3AE%2C%204%2C%20FALSE)%2C%26nbsp%3BVLOOKUP(A3%2C%20inventory!A%3AE%2C%205%2C%20FALSE))%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EGrapes%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECola%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Worksheet%202%3A%20Inventory%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3EFood%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EMains%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ESides%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EHealthy%20Desserts%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EUnhealthy%20Desserts%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3EApple%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EApple%20Chips%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EApple%20Pie%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3EBanana%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EBanana%20Pancakes%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3EChocolate%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EChocolate%20Cake%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3ECarrot%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ECarrot%20Sticks%20with%20Dip%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ECarrot%20Cake%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20in%20this%20example%2C%20what%20I%20trying%20to%20do%20is%20for%20Chocolate%2C%20I%20want%20it%20to%20check%20the%20blanks%2C%20and%20if%20the%20cell%20is%20blank%20move%20to%20the%20next%20cell%3B%20however%2C%20once%20it%20finds%20a%20value%20in%20the%20specified%20Chocolate%20row%2C%20I%20want%20it%20to%20return%20that%20value%20in%20List%20cell%20on%20the%20other%20table...%20So%20in%20the%20Unhealthy%20Desserts%20option%20on%20the%20Lists%20Workbook%20of%20the%20Chocolate%20Row%2C%20I%20want%20Chocolate%20Cake%20to%20be%20populated%20as%20the%20value...%20right%20now%20I%20am%20getting%20%23N%2FA.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20the%20example%20is%20a%20sample%20of%20the%20sort%20of%20workbook%20I%20am%20using%20and%20VLOOKUP%20works%20great%26nbsp%3B%20comparing%20the%202%20sheets%20if%20I%20only%20have%20a%20single%20row%20to%20check%20(e.g.%20like%20Mains%2C%20but%20not%20if%20I%20have%20more%20than%20one%20column%20I%20need%20to%20check%2C%20like%20Desserts)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2268127%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2268516%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20multiple%20cells%2C%20skipping%20if%20blank%20and%20returning%20the%20value%20of%20the%20cell%20once%20it%20is%20found%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2268516%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1023771%22%20target%3D%22_blank%22%3E%40shannonholmes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20think%20you%20want%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(LEN(VLOOKUP(A3%2C%20inventory!A%3AE%2C%204%2C%20FALSE))%3D0%2C%20VLOOKUP(A3%2C%20inventory!A%3AE%2C%205%2C%20FALSE)%2C%20VLOOKUP(A3%2C%20inventory!A%3AE%2C%204%2C%20FALSE))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2269646%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20multiple%20cells%2C%20skipping%20if%20blank%20and%20returning%20the%20value%20of%20the%20cell%20once%20it%20is%20found%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2269646%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bthat%20is%20still%20giving%20me%20%23N%2FA%20instead%20of%20the%20value%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have tried all sorts of fomula's and the current version that I have is 

=IF(LEN(VLOOKUP(A2, inventory!A:E, 3, FALSE))=0,VLOOKUP(A2, inventory!A:E, 3, FALSE), VLOOKUP(A2, inventory!A:E, 4, FALSE))

 

What I am trying to do, is I have 2 worksheets in a workbook:

list and inventory

 

Worksheet 1:  List

 

FoodHealthy DessertsUnhealthy Desserts
Apple  
Chocolate =IF(LEN(VLOOKUP(A3, inventory!A:E, 3, FALSE))=0,VLOOKUP(A3, inventory!A:E, 4, FALSE), VLOOKUP(A3, inventory!A:E, 5, FALSE))
Grapes  
Cola  

 

In Worksheet 2: Inventory

 

FoodMainsSidesHealthy DessertsUnhealthy Desserts
Apple  Apple ChipsApple Pie
BananaBanana Pancakes   
Chocolate   Chocolate Cake
Carrot  Carrot Sticks with DipCarrot Cake

 

So, in this example, what I trying to do is for Chocolate, I want it to check the blanks, and if the cell is blank move to the next cell; however, once it finds a value in the specified Chocolate row, I want it to return that value in List cell on the other table... So in the Unhealthy Desserts option on the Lists Workbook of the Chocolate Row, I want Chocolate Cake to be populated as the value... right now I am getting #N/A.  

 

Please note the example is a sample of the sort of workbook I am using and VLOOKUP works great  comparing the 2 sheets if I only have a single row to check (e.g. like Mains, but not if I have more than one column I need to check, like Desserts)

10 Replies

@shannonholmes 

I think you want

 

=IF(LEN(VLOOKUP(A3, inventory!A:E, 4, FALSE))=0, VLOOKUP(A3, inventory!A:E, 5, FALSE), VLOOKUP(A3, inventory!A:E, 4, FALSE))

@Hans Vogelaar that is still giving me #N/A instead of the value 

@shannonholmes 

Try this in B2:

 

=IFERROR(VLOOKUP(A2,Inventory!A:E,4,FALSE)&"","")

 

And in C2:

 

=IFERROR(VLOOKUP(A2,Inventory!A:E,5,FALSE)&"","")

 

Fill down

@Hans Vogelaar That is only the single formula and it is not ignoring the blanks and populating the cell when it finds a value.  

 

That formula is okay if there is only a single cell I need it to check, however, I have a table that has multiple options in 2 or 3 columns and I need the formula to ignore the blanks and return the value from one of the cells.

 

Maybe that example was not the ideal, perhaps this is better - will still use the same table names

 

Table 1: List

website:Begin to Code BookJava book
amazon.com=IF(LEN(VLOOKUP(A2, INVENTORY!A:D,2,FALSE))=0, VLOOKUP(A2, INVENTORY!A:D,3,FALSE), VLOOKUP(A2, INVENTORY!A:D,4,FALSE) = for Amazon, I want it to check Java first, find nothing, then check Python, find something and so return that result) 
google.com =IFNA(VLOOKUP(A3, INVENTORY!A:D,2,FALSE), "N/A")  =  will return Java for Breakfast  (Already am using this if only a single column corresponds to the criteria, but in some cases I have multiple columns that it needs to check and find the value from - as with the Amazon example)
   

 

Table 2: Inventory

websiteJava bookPython bookRuby book
amazon.com Python for Dummies 
ebay.comA Taste of Java Ruby for beginners
google.comJava for breakfast  
best response confirmed by shannonholmes (Occasional Contributor)
Solution

@shannonholmes 

Try this then:

 

=IFERROR(IF(VLOOKUP(A3,Inventory!A:E,3,FALSE)&""="",IF(VLOOKUP(A3,Inventory!A:E,4,FALSE)&""="",IF(VLOOKUP(A3,Inventory!A:E,5,FALSE)&""="","",VLOOKUP(A3,Inventory!A:E,5,FALSE)),VLOOKUP(A3,Inventory!A:E,4,FALSE)),VLOOKUP(A3,Inventory!A:E,3,FALSE)),"")

That is returning the result I need.

If it is ok, just want to check my understanding of the logic here...

IFERROR - will catch the error
IF
then it does VLookup - checks Amazon matches and checks the cell and if is empty (&""="",) then moves on to the next cell? That is the first half correct? =IFERROR(IF(VLOOKUP(A3,Inventory!A:E,3,FALSE)&""="",IF(VLOOKUP(A3,Inventory!A:E,4,FALSE)&""="",IF(VLOOKUP(A3,Inventory!A:E,5,FALSE)&""="",

After the third VLoopup there is an extra , "" and the 3 VLookups again, is this to populate the cell?

@shannonholmes The "" after the first 3 VLOOKUPs makes the formula return true if all 3 returned a blank. The 3 VLOOKUPs after that, in reverse order, populate the cell with the VLOOKUP that returned a non-blank value.

Perfect! Thank you for all your help! I truly appreciate it!

@Hans Vogelaar I am trying something similar, however, I am checking 8 different cells rather than 3. I have copied the syntax you have described however I am getting a message saying that there is a problem with my formula:

 

IFERROR(IF(VLOOKUP(A3,Form1!$A:$X,13,FALSE)&""="",IF(VLOOKUP(A3,Form1!$A:$X,14,FALSE)&""="",IF(VLOOKUP((A3,Form1!$A:$X,14,FALSE)&""="",IF(VLOOKUP((A3,Form1!$A:$X,15,FALSE)&""="",IF(VLOOKUP((A3,Form1!$A:$X,16,FALSE)&""="",IF(VLOOKUP((A3,Form1!$A:$X,17,FALSE)&""="",IF(VLOOKUP((A3,Form1!$A:$X,18,FALSE)&""="",IF(VLOOKUP((A3,Form1!$A:$X,19,FALSE)&""="",IF(VLOOKUP((A3,Form1!$A:$X,20,FALSE)&""="",VLOOKUP(A3,Form1!$A:$X,20,FALSE)),VLOOKUP(A3,Form1!$A:$X,19,FALSE)),VLOOKUP(A3,Form1!$A:$X,18,FALSE)),VLOOKUP(A3,Form1!$A:$X,17,FALSE)),VLOOKUP(A3,Form1!$A:$X,16,FALSE)),VLOOKUP(A3,Form1!$A:$X,15,FALSE)),VLOOKUP(A3,Form1!$A:$X,14,FALSE)),VLOOKUP(A3,Form1!$A:$X,13,FALSE)),"")

 

Any guidance would be helpful.

 

Thanks

@djlw84 

You have several times (( instead of (, and column index 14 is used twice in the IF parts.

You could use

=IFERROR(
  IF(VLOOKUP(A3,Form1!$A:$X,13,FALSE)&""="",
    IF(VLOOKUP(A3,Form1!$A:$X,14,FALSE)&""="",
      IF(VLOOKUP(A3,Form1!$A:$X,15,FALSE)&""="",
        IF(VLOOKUP(A3,Form1!$A:$X,16,FALSE)&""="",
          IF(VLOOKUP(A3,Form1!$A:$X,17,FALSE)&""="",
            IF(VLOOKUP(A3,Form1!$A:$X,18,FALSE)&""="",
              IF(VLOOKUP(A3,Form1!$A:$X,19,FALSE)&""="",
                VLOOKUP(A3,Form1!$A:$X,20,FALSE),
                VLOOKUP(A3,Form1!$A:$X,19,FALSE)),
              VLOOKUP(A3,Form1!$A:$X,18,FALSE)),
            VLOOKUP(A3,Form1!$A:$X,17,FALSE)),
          VLOOKUP(A3,Form1!$A:$X,16,FALSE)),
        VLOOKUP(A3,Form1!$A:$X,15,FALSE)),
      VLOOKUP(A3,Form1!$A:$X,14,FALSE)),
    VLOOKUP(A3,Form1!$A:$X,13,FALSE)),
  "")

But the following array formula confirmed with Ctrl+Shift+Enter is shorter:

=IFERROR(INDEX(INDEX(Form1!$M:$T,MATCH(A3,Form1!$A:$A,0),0),MATCH(TRUE,INDEX(Form1!$M:$T,MATCH(A3,Form1!$A:$A,0),0)<>"",0)),"")