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!

 

[Aladin Akyurek]

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)))}

I think you need to re-think what the output would be...

For example, the MAX value can be associated with the following headers:

Sheet1 >> A1, C1
Sheet2 >> B1, B1
Sheet3 >> A1, B1

I hope you are not going to say that you want them all...

 

[wildchild69]

I need to search the same areas on each sheet. If it doesnt find the value on sheet 1 then search sheet 2 and so on.

 

[Samosa]

Hi, thanks for your reply. The solution worked for F2, but didn't seem to work when copied to other cells..

I changed the cell references to fixed values. The lookup worked for letter h in finding d, but it didnt work for the others.
The yellow highlighted cell should display 'g' and the formula in that cell now reads:

{=IFERROR(INDEX($A$2:$C$5,MIN(IF($A$2:$C$5=E3,ROW($A$2:$C$5)-ROW(A3)+1)), 1/(1/MAX(IF($A$2:$C$5=E3,COLUMN($A$1:$C$1)-COLUMN($A$1)+1)-1))),"")}
Thanks!

				lookup	result
A	e	i		h	d
B	f	j		k	f
C	g	k		l	f
d	h	l		f

<COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY>
</TBODY>

 

[Aladin Akyurek]

Hi, thanks for your reply. The solution worked for F2, but didn't seem to work when copied to other cells..

I changed the cell references to fixed values. The lookup worked for letter h in finding d, but it didnt work for the others.
The yellow highlighted cell should display 'g' and the formula in that cell now reads:

{=IFERROR(INDEX($A$2:$C$5,MIN(IF($A$2:$C$5=E3,ROW($A$2:$C$5)-ROW(A3)+1)), 1/(1/MAX(IF($A$2:$C$5=E3,COLUMN($A$1:$C$1)-COLUMN($A$1)+1)-1))),"")}
Thanks!

				lookup	result
A	e	i		h	d
B	f	j		k	f
C	g	k		l	f
d	h	l		f

<tbody>
</tbody>

Thjs is what I get...

~

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

F2, control+shift+enter, not just enter, and copy down:

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

 

[Aladin Akyurek]

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

Which formula are you addressing?

My apologies. This one.
{=INDEX(A1:C1,MAX(IF(A2:C5=E2,COLUMN(A2:C5)-COLUMN(A1)+1)))}

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)))}

I think you need to re-think what the output would be...

For example, the MAX value can be associated with the following headers:

Sheet1 >> A1, C1
Sheet2 >> B1, B1
Sheet3 >> A1, B1

I hope you are not going to say that you want them all...

I need to search the same areas on each sheet. If it doesnt find the value on sheet 1 then search sheet 2 and so on.

1. Create a range housing the relevant sheet names, select this range, name the selection SheetList.

2. The formula sheet, that is, the sheet the following formulas need to be implemented. Note that the data area in very sheet is A1:C5 with A1:C1 housing the same header fields (in what follows: A1 = VAD, B1 = JAD, and C1 = NAD).

Row\Col	A​
1​
2​	88​
3​	2​
4​	NAD
5​

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

=MAX(SUBTOTAL(4,INDIRECT("'"&SheetList&"'!A2:C5")))

This yields the MAX value.

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

=MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:C5"),A2)>0,0)

This yields the position in SheetList of the sheet that contains the MAX value.

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

=INDEX(INDIRECT("'"&INDEX(SheetList,A3)&"'!A1:C1"),
    MIN(IF(INDIRECT("'"&INDEX(SheetList,A3)&"'!A2:C5")=A2,
    COLUMN(INDIRECT("'"&INDEX(SheetList,A3)&"'!A2:C5")))))

This yields the header field under which the first MAX instance is located.

 

[yrubin]

Aladin...I'm trying to search in A1 for any of the strings in A4:H8 then return the corresponding value from column A. For example; I see that the exact string in F7 is located somewhere in A1, so I'd like to return A7 (12oz). Any ideas? Thanks in advance!

Orange Juice 12 Oz Trop
Sizes	Variations
8oz	8Oz	8oZ	8OZ	8 oz	8 Oz	8 oZ	8 OZ
8.4oz	8.4Oz	8.4oZ	8.4OZ	8.4 oz	8.4 Oz	8.4 oZ	8.4 OZ
11.15oz	11.15Oz	11.15oZ	11.15OZ	11.15 oz	11.15 Oz	11.15 oZ	11.15 OZ
12oz	12Oz	12oZ	12OZ	12 oz	12 Oz	12 oZ	12 OZ
14.9oz	14.9Oz	14.9oZ	14.9OZ	14.9 oz	14.9 Oz	14.9 oZ	14.9 OZ

<tbody>
</tbody>

I haven't been able to make vlookup or index-match-match work for my purposes.

 

[Aladin Akyurek]

@yrubin

It looks like the following would suffice...

In B1 enter:

=LOOKUP(9.99999999999999E+307,SEARCH(SUBSTITUTE($A$4:$A$8," ",""),
  SUBSTITUTE($A$1," ","")),$A$4:$A$8)

 

[yrubin]

Amazing!! Would you mind explaining how that works? I get why you'd remove the spaces, but I don't understand the search and output parameters. Thank you so much!

 

[Aladin Akyurek]

Amazing!! Would you mind explaining how that works? I get why you'd remove the spaces, but I don't understand the search and output parameters. Thank you so much!

See: http://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html#post3568323

 

[Donald Leigh]

Can someone please help me with this. I have a table as below. I would like to look up a number (say 3454) and return the number in the 1st column. (2)

A B C D Lookup Answer 1 1234 43556 3435 2 2 5678 3435 3 90123 4 74547 234 234 <colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>