Apr 12 2021 08:53 AM
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)
Apr 12 2021 11:27 AM
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))
Apr 13 2021 01:13 AM
@Hans Vogelaar that is still giving me #N/A instead of the value
Apr 13 2021 03:49 AM
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
Apr 13 2021 04:45 AM
@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 |
Apr 13 2021 05:34 AM
SolutionTry 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)),"")
Apr 13 2021 05:59 AM
Apr 13 2021 06:27 AM
@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.
Apr 13 2021 06:29 AM
Jul 29 2021 09:09 AM
@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
Jul 29 2021 01:17 PM
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)),"")