 SOLVED

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

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

 Food Healthy Desserts Unhealthy 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

 Food Mains Sides Healthy Desserts Unhealthy Desserts Apple Apple Chips Apple Pie Banana Banana Pancakes Chocolate Chocolate Cake Carrot Carrot Sticks with Dip Carrot 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)

8 Replies

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

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))

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

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

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

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

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

@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 Book Java 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

 website Java book Python book Ruby book amazon.com Python for Dummies ebay.com A Taste of Java Ruby for beginners google.com Java for breakfast
best response confirmed by shannonholmes (Occasional Contributor)
Solution

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

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)),"")

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

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?

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

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

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

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