Return column header

[paulevans85]

Ok so I have two spreadsheets: One has a column (say C) that is a list of text strings and the other has a big table made up of said text strings. This table has each of the strings organized by the group it belongs to with the name of that group as the column header. What I'm trying to do is match each string in the first spreadsheet to the second one and return the header of the column in which it appears.

Example:

File 1:
c d
1 text1 ?
2 text2 ?
3 text3 ?

File 2:
a b c
1 group1 group2 group3
2 text4 text3 text2
3 text4 text5 text2
4 text1 text6

So for C1 of the first file, I want to somehow output (say in D1) "group1."

The code I have thus far is

=INDEX('[File 2.xls]Sheet 1'!$A$1:$C$4,1,MATCH(C1,'[File 2.xls]Sheet 1'!$A$4:$C$4,FALSE))

but I couldn't figure out how to get it to iterate through the rows of data (without the use of a macro/VB) and so I kinda got the feeling I was on the wrong track.

Thanks in advance,

Paul

 

[DonkeyOte]

if you really only have three columns to check you could try something like the below (avoiding arrays)

=INDEX('[File 2.xls]Sheet 1'!$A$1:$C$1,0,LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(C1,'[File 2.xls]Sheet 1'!$A$1:$A$4,0),MATCH(C1,'[File 2.xls]Sheet 1'!$B$1:$B$4,0),MATCH(C1,'[File 2.xls]Sheet 1'!$C$1:$C$4,0))))

 

[paulevans85]

Yeah I was going to try something like that with IFs and/or ISERRORs but there are over 14K rows and 7 columns.

 

[DonkeyOte]

could be streamlined with INDIRECT to account for second columns without having to repeat match -- not have assumed range A1:G14000

=INDEX([File 2.xls]Sheet 1'!$A$1:$G$1,0,LOOKUP(9.99999999999999E+307,MAX(MATCH(C1,INDIRECT("'[File 2.xls]Sheet 1'!"&CHOOSE({1,2,3,4,5,6,7},"A1:A14000","B1:B14000","C1:C14000","D1:D14000","E1:E14000","F1:F14000","G1:G14000")),0))))

HTH

 

[Aladin Akyurek]

File 1, D1:

Control+shift+enter, not just enter...

=INDEX('[File 2.xls]Sheet1'!$A$1:$C$1,
   MIN(IF('[File 2.xls]Sheet1'!$A$2:$C$4=C1,
      COLUMN('[File 2.xls]Sheet1'!$A$2:$C$4)-COLUMN('[File 2.xls]Sheet1'!$A$2)+1)))

...and copy down.

 

[DonkeyOte]

so simple yet so good...

(and suprise surprise my *streamlined* effort wouldn't have worked anyway)

Aladin, you should write a book...really.

 

[paulevans85]

Thanks for the replies.

I put in the formula Aladin supplied and I get #NA and after a while it says there aren't enough resources, I even tried it just after saving. I could have just put it in incorrectly. I don't have enough time to mess around with it right now but should tomorrow so I'll get back to you with my results then.

 

[paulevans85]

So it worked really well except that it always returns the first column header if if the string doesn't exist (which, I probably should have mentioned, happens).

Is there a way to get it to throw an error or 0 if it doesn't exist?