If not found....

[Irukandji81]

Hey guys,

I have a spreadsheet I am working on where there are multiple input tabs and a master tab that pulls all the details into one. I created this formula to basically search through all the tabs and pull the number from the associated tab based on a unique ID number:

=IFERROR(INDEX(Tab1!L:L,MATCH(G2,Tab1!E:E,0)), IFERROR(INDEX('Tab2'!L:L,MATCH(G2,'Tab2'!E:E,0)), IFERROR(INDEX(Tab3!L:L,MATCH(G2,Tab3!E:E,0)), INDEX(Tab4!L:L,MATCH(G2,Tab4!E:E,0)))))

The problem I am having is figuring out how to make it not give a "#N/A" if the UID is not found. I would love for it to just give a "-" if there is nothing found that matches. Any thoughts?

Thanks,
Nathan

[sandy666]

maybe use total IFERROR(your_whole_formula,"-")

[Irukandji81]

I knew it had to be something simple. Thank you!

[sandy666]

You are welcome

[Irukandji81]

I am going to open this one back up because I have a followup issue with it now. The formula is working perfectly except for one minor detail. Now, when it searches the tabs and DOES find a unique ID, but the called cell is blank it is putting a "0" in the cell. How do I make it if it detects the cell as blank to put a "-" in the cell as well?

Here is my formula as it stands now:

=IFERROR(IFERROR(INDEX(Tab1!H:H,MATCH($G2,Tab1!$G:$G,0)), IFERROR(INDEX('Tab2'!H:H,MATCH($G2,'Tab2'!$G:$G,0)), IFERROR(INDEX(Tab3!H:H,MATCH($G2,Tab3!$G:$G,0)), INDEX(Tab4!H:H,MATCH($G2,Tab4!$G:$G,0))))),"-")

[sandy666]

you can try untick this option to "show" blank instead of zero. (I don't remember where it is in Ex2007)

showzero.jpg

If you really want "-" your formula will be a monster

=IF(OR(ISERROR(IFERROR(INDEX(Tab1!H:H,MATCH($G2,Tab1!$G:$G,0)), IFERROR(INDEX('Tab2'!H:H,MATCH($G2,'Tab2'!$G:$G,0)), IFERROR(INDEX(Tab3!H:H,MATCH($G2,Tab3!$G:$G,0)), INDEX(Tab4!H:H,MATCH($G2,Tab4!$G:$G,0)))))),IFERROR(INDEX(Tab1!H:H,MATCH($G2,Tab1!$G:$G,0)), IFERROR(INDEX('Tab2'!H:H,MATCH($G2,'Tab2'!$G:$G,0)), IFERROR(INDEX(Tab3!H:H,MATCH($G2,Tab3!$G:$G,0)), INDEX(Tab4!H:H,MATCH($G2,Tab4!$G:$G,0)))))=0),"-",IFERROR(INDEX(Tab1!H:H,MATCH($G2,Tab1!$G:$G,0)), IFERROR(INDEX('Tab2'!H:H,MATCH($G2,'Tab2'!$G:$G,0)), IFERROR(INDEX(Tab3!H:H,MATCH($G2,Tab3!$G:$G,0)), INDEX(Tab4!H:H,MATCH($G2,Tab4!$G:$G,0))))))

not tested. you need to check it

edit:
=IF(OR(ISERROR(your_formula),your_formula=0),"-",your_formula)
where your_formula is:
IFERROR(INDEX(Tab1!L:L,MATCH(G2,Tab1!E:E,0)), IFERROR(INDEX('Tab2'!L:L,MATCH(G2,'Tab2'!E:E,0)), IFERROR(INDEX(Tab3!L:L,MATCH(G2,Tab3!E:E,0)), INDEX(Tab4!L:L,MATCH(G2,Tab4!E:E,0)))))

[Irukandji81]

I liked the idea of having something in the cell so it shows that it returned nothing but I'd rather not make the formula that crazy so I will use the checkbox option Nothing looks better than a 0. And I think as long as the sheet is protected so no one tries to type in the field it should be good! Thanks!!

[sandy666]

You are welcome

this is your opinion about blank than zero I prefer zeroes

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
If you did it - ignore it.
Thank you.