Nesting Iferror or Isblank into Index/Match

[sultana]

Hello from downunder (Sydney),

Im trying to create an item master from approx. 8 spreadsheets. All in different formats. Most have duplicate unique identifiers (SKU code), but some sheets have dirty incorrect data. Ive therefore determined a sequence based on the cleanest source.

I am using the Index/Match formula into a master sheet to consolidate and return 100,000 rows and 50 columns of data. From here, I would like to keep searching through the next workbook (using index/match) if the initial search result returns an error "N/A" or if is blank.

I cant seem to figure out the Isblank bit.

Here is what I have got so far. I have 6 more "tables" to search through.

=IFERROR(INDEX('table 1'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 1'!$AG:$AG,0)),INDEX(table 2'!F:F,MATCH('Crimson- Item Master WIP'!$F78686,'table2'!$A:$A,0)))

Thanks kindly for your help. Pulling my hair out!

 

[steve the fish]

All you need is to understand that the value if error can also be another IFERROR statement. That is how you can nest them.

 

[steve the fish]

Heres a very simple nested iferror:

=IFERROR(A1,IFERROR(B1,IFERROR(C1,IFERROR(D1,E1))))

 

[sultana]

I just assumed Index/Match works like vlookup, meaning if the "sku"is found on the first table but the data cell is blank it will still see this has a match and not an error. Therefore wont follow the next nested index?

 

[steve the fish]

Correct in that instance the formula would return a blank.

 

[sultana]

Correct in that instance the formula would return a blank.

Yes that is the problem! So where the formula returns and error OR returns a blank, then Id like to follow the sequence and check the next worksheet table and so on.

 

[steve the fish]

Not the nicest these nested formulas with conditions:

=IF(AND(IFERROR(LEN(INDEX('table 1'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 1'!$AG:$AG,0)))>0,0),NOT(ISERROR(INDEX('table 1'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 1'!$AG:$AG,0))))),INDEX('table 1'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 1'!$AG:$AG,0)),IF(AND(IFERROR(LEN(INDEX('table 2'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 2'!$AG:$AG,0)))>0,0),NOT(ISERROR(INDEX('table 2'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 2'!$AG:$AG,0))))),INDEX('table 2'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 2'!$AG:$AG,0)),"next formula"))

 

[sultana]

Not the nicest these nested formulas with conditions:

=IF(AND(IFERROR(LEN(INDEX('table 1'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 1'!$AG:$AG,0)))>0,0),NOT(ISERROR(INDEX('table 1'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 1'!$AG:$AG,0))))),INDEX('table 1'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 1'!$AG:$AG,0)),IF(AND(IFERROR(LEN(INDEX('table 2'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 2'!$AG:$AG,0)))>0,0),NOT(ISERROR(INDEX('table 2'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 2'!$AG:$AG,0))))),INDEX('table 2'!AI:AI,MATCH('Crimson- Item Master WIP'!$Z78686,'table 2'!$AG:$AG,0)),"next formula"))

Oh wow!!! Thank you so so so much for helping me with this. Of course! That makes so much sense - you taught me something new. Thank you - worked perfect!!!!!!!!!!! Now to wait for the stupid sheet to calculate LOL.