Finding a value in a multiple column array and returning column header - Excel 2003

[NairbNam]

Hi guys first post here.

I am attempting to lookup a cell value in a 3 column by 20 row array. Depending on which column the value is found, I would like to return that column's header value.

I think I am making this overly complicated for myself. Basically I need a statement that will perform an Hlookup on three columns but I'm not sure exactly how to do this.

Any help would be appreciated.

Thanks!

 

[wildchild69]

Could I use this to search multiple sheets? and how?

 

[Aladin Akyurek]

Could I use this to search multiple sheets? and how?

Which formula are you addressing?

 

[wildchild69]

My apologies. This one.

{=INDEX(A1:C1,MAX(IF(A2:C5=E2,COLUMN(A2:C5)-COLUMN(A1)+1)))}

 

[Samosa]

Hello, how would the formula read if I wanted to return the value to column to the left of the found value rather than the column header (ie the solution in F2 would 48). Also should clarify that I'd be looking for text values rather than numeric. Thanks in advance!

 

[Aladin Akyurek]

Hello, how would the formula read if I wanted to return the value to column to the left of the found value rather than the column header (ie the solution in F2 would 48). Also should clarify that I'd be looking for text values rather than numeric. Thanks in advance!

Try to post a sample sample along with the desired output.

 

[Samosa]

Sorry, I was referring to this sample posted previously. I would want F2 to search for H in columns A through C and a return "D", the value immediately to the left.
Thanks!

	A	B	C	D	E	F
1	Header1	Header2	Header3	_	Lookup	SOLUTION
2	A	E	I	_	H	D
3	B	F	J	_	_	_
4	C	G	K	_	_	_
5	D	H	L	_	_	_

<TBODY>
</TBODY>

 

[Ingolf]

And if you look for D from first column, what is letter to expect?

 

[Samosa]

And if you look for D from first column, what is letter to expect?

Nothing.

 

[Aladin Akyurek]

Sorry, I was referring to this sample posted previously. I would want F2 to search for H in columns A through C and a return "D", the value immediately to the left.
Thanks!

	A	B	C	D	E	F
1	Header1	Header2	Header3	_	Lookup	SOLUTION
2	A	E	I	_	H	D
3	B	F	J	_	_	_
4	C	G	K	_	_	_
5	D	H	L	_	_	_

<tbody>
</tbody>

In F2 control+shift+enter, not just enter:

=IFERROR(INDEX($A$2:$C$5,MIN(IF(A2:C5=E2,ROW(A2:C5)-ROW(A2)+1)),
    1/(1/MAX(IF(A2:C5=E2,COLUMN(A1:C1)-COLUMN(A1)+1)-1))),"")

 

[wildchild69]

Could I use this formula to search multiple sheets? and how?
{=INDEX(A1:C1,MAX(IF(A2:C5=E2,COLUMN(A2:C5)-COLUMN(A1)+1)))}