Lookup multiple Columns & show which column the data is returned from

[Davcas]

Hi, I am using the following formula to Lookup multiple Columns if data is not found in column 1, but because some details have been duplicated in the tables I need to show which column the data is coming back from. How can I do this?

=IFERROR(VLOOKUP(A2,'[Common Database.xlsx]Sheet1'!$A:$G,1,FALSE),IFERROR(VLOOKUP(B2,'[Common Database.xlsx]Sheet1'!$B:$G,1,FALSE),IFERROR(VLOOKUP(C2,'[Common Database.xlsx]Sheet1'!$C:$G,1,FALSE),"")))

[Mallycat]

It is very difficult to work out what this formula does without the detailed sheets. Are you saying this formula returns data from a column, but you don't know which one? Is it possible to put a formula in another cell that tells you the column? Presumably you have 2 peices of data. one is the result, and the second is the column it came from.

[Davcas]

I have a table with names, asset tags & serial numbers and am trying to compare my table with another teams to check we have the same but they use different names, so i am trying to then match on either asset or serial and return the name, but i need to define which field i actually got the match on as I know some details have been duplicated so i might return a different set of data to that I need.

So If I get a match from one of the 3 column headings and my return each time is the server name ,I need to know which field I matched the data on?

[vlady]

so there would be two results the value and the column where that value is...

just an idea.. you can have a helper column of some sort that will tell you what column that value was.

like:
=IFERROR(VLOOKUP(A2,'[Common Database.xlsx]Sheet1'!$A:$G,1,FALSE)&""&"this is from column AA"
******
there are formulas to return the column title but integrating it to your 3 lookup formula i think is not advisable.

here,s a sample of getting the column title : got it from tiger--
returns the column title...
=IFERROR(INDEX($A$1:$K$1,,SMALL(IF(OFFSET($B$1:$K$1,MATCH($B$14,$A$2:$A$9,0),)="PASS",COLUMN($B$1:$K$1)),COUNTA($B$14:B14))),"")

if you can pull this off you can integrate it with your 3 lookups..
good luck.

[Davcas]

[QUOTE=vlady;2765999]so there would be two results the value and the column where that value is...
here,s a sample of getting the column title : got it from tiger--
returns the column title...
=IFERROR(INDEX($A$1:$K$1,,SMALL(IF(OFFSET($B$1:$K$1,MATCH($B$14,$A$2:$A$9,0),)="PASS",COLUMN($B$1:$K$1)),COUNTA($B$14:B14))),"")

Thanks, but as a newbie to excel fomulas the formula is a bit of a minefield. What is the small wording ?