SOLVED

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

Copper 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 (Copper 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)),"")
1 best response

Accepted Solutions
best response confirmed by shannonholmes (Copper 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)),"")

View solution in original post