Forum Discussion
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)
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)),"")
12 Replies
"To resolve the issue you're facing with checking multiple columns for values in your VLOOKUP formula, you might want to try using the IFERROR function to handle missing values. Here's an improved version of your formula:
=IFERROR(VLOOKUP(A3, inventory!A:E, 3, FALSE), IFERROR(VLOOKUP(A3, inventory!A:E, 4, FALSE), VLOOKUP(A3, inventory!A:E, 5, FALSE)))
This checks the columns one by one, skipping if the cell is blank or returns an error, and stops once it finds a value.
Also, if you're looking for some comfort food ideas while working on your Excel sheet, check out https://portillos-menu.com/ for some delicious options!"
"To resolve the issue you're facing with checking multiple columns for values in your VLOOKUP formula, you might want to try using the IFERROR function to handle missing values. Here's an improved version of your formula:
=IFERROR(VLOOKUP(A3, inventory!A:E, 3, FALSE), IFERROR(VLOOKUP(A3, inventory!A:E, 4, FALSE), VLOOKUP(A3, inventory!A:E, 5, FALSE)))
This checks the columns one by one, skipping if the cell is blank or returns an error, and stops once it finds a value.
Also, if you're looking for some comfort food ideas while working on your Excel sheet, check out https://portillos-menu.com/ for some delicious options!"
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))
HansVogelaar that is still giving me #N/A instead of the value
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
