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

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


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


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 


Try this in B2:




And in C2:




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, 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) =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 Python for Dummies 
ebay.comA Taste of Java Ruby for beginners
google.comJava for breakfast  
best response confirmed by shannonholmes (Occasional Contributor)


Try this then:



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




Any guidance would be helpful.




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

You could use


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