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)),"")
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 |
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)),"")
HansVogelaar 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
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)),"")
- 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?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!
