Flagging vlookup results based on which argument it came from

[Thom7890]

Hello, I've created the following formula to draw vlookup data from three different tables on the 'New Held Batches Sheet' sheet.
It checks the first table for a match and if there is an error it moves onto the next table, etc.
Unrelatedly, it also creates a blank if the data is not on any table.

Is there a way to flag the results based on which table the resulting data came from?

e.g. the data came from the second table so it is blue, or the data came from the first table so it is red.


=IFERROR(IFERROR(VLOOKUP(C2,'New Held Batches Sheet'!$C$5:$D$5,2,0),IFERROR(VLOOKUP(C2,'New Held Batches Sheet'!$C$8:$D$22,2,0),VLOOKUP(C2,'New Held Batches Sheet'!$C$25:$D$35,2,0))),"")

Thanks for all the guidance through the years.

[tim201110]

=(IF(ISERR(VLOOKUP(C2,'New Held Batches Sheet'!$C$5:$D$5,2,0),,1)+IF(ISERR(VLOOKUP(C2,'New Held Batches Sheet'!$C$8:$D$22,2,0),,2)+ and so on)=1 or 2
why not =VLOOKUP(C2,'New Held Batches Sheet'!$C$5:$D$35,2,0)?

[MrShorty]

Conditional formatting cannot "see" inside of the execution of a function like this. A couple of approaches:

1) Include something in the output of the function that will indicate which table it came from. Maybe something like =IFERROR(IFERROR("a"&VLOOKUP(C2,'New Held Batches Sheet'!$C$5:$D$5,2,0),IFERROR("b"&VLOOKUP(C2,'New Held Batches Sheet'!$C$8:$D$22,2,0),"c"&VLOOKUP(C2,'New Held Batches Sheet'!$C$25:$D$35,2,0))),""). If you still need the conditional formatting, then you can have your conditional formatting check for the leading character (a, b, or c) and format accordingly. The main thing I don't like about this approach is that it adds a piece of information to the result, which could cause difficulties for other formulas that need this result.

2) (My personal favorite) Use helper cells to return the result from each lookup. cell1 =VLOOKUP(C2,'New Held Batches Sheet'!$C$5:$D$5,2,0) cell2 =VLOOKUP(C2,'New Held Batches Sheet'!$C$8:$D$22,2,0) cell3 =VLOOKUP(C2,'New Held Batches Sheet'!$C$25:$D$35,2,0). Then a 4th cell could look at those three and return the value from the cell that is not N/A, and your conditional formatting could look at those three cells and format according to which one is not N/A.

[Thom7890]

Thank you for the great ideas - I think your #1 idea shorty is what will work best for me. I just need a way of personally knowing where the data came from - the formatting is unimportant.

[protonLeah]

Alternatively, just use each of the three parts of the original formula in its own Conditional Format formula. The first applies blue fill, the second red(!), etc.