Forum Discussion
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)),"")
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
HansVogelaar 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
