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]

This thread appeared to be exactly what I was looking for, but all my attempts to get it to work on my spreadsheet return errors.

I have a table consisting of numbers and blank cells from G11:LL330. I want to find where a number,let's say "14," is located in this table and copy the contents of the cell in Row 1 of the column where 14 was found into cell A1. Then I want to copy the contents of the cell in Column 1 of the row where 14 was found into cell A2. It sure sounds like the same problem described above, but I'm missing something.

Oh, and I'm using EXCEL 2010

Please post a small sample, say G11:K15, the input and the expected results (the output), which can be copied directly into Excel cells.

 

[excelneedshelp]

Hey! After trying out this formula, it worked perfectly but i do not understand how it works ): Do you mind explaining and guide me through the steps?

Thanks and regards,
Excelneedshelp

 

[excelneedshelp]

Try this...

Sheet1

	A	B	C	D	E	F
1	Header1	Header2	Header3	_	Lookup	Location
2	22	10	65	_	94	Header2
3	44	15	59	_	_	_
4	48	94	31	_	_	_
5	33	29	86	_	_	_

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:72px;"><col style="width:72px;"><col style="width:72px;"><col style="width:20px;"><col style="width:72px;"><col style="width:72px;"></colgroup><tbody>
</tbody>

This array formula** entered in F2:

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

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hey! After trying out this formula on Excel2013, it worked perfectly but i do not understand how it works ): Do you mind explaining and guide me through the steps? Please do reply if you see this

Thanks and regards,
Excelneedshelp

 

[Aladin Akyurek]

Hey! After trying out this formula on Excel2013, it worked perfectly but i do not understand how it works ): Do you mind explaining and guide me through the steps? Please do reply if you see this

Thanks and regards,
Excelneedshelp

The IF bit in

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

delivers column numbers of cells in A2:C5 which are equal to to the value of E2.

MAX picks out the highest column number and feeds that number to INDEX.

INDEX yields the value from A1:C1 at that column number.

For the sample the exhibit shows we get successively...

INDEX(A1:C1,MAX({FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,2,FALSE;FALSE,FALSE,FALSE}))

==>

=INDEX(A1:C1,2)

==>

Header2

Hope this helps.

 

[jericksonapga]

Please post a small sample, say G11:K15, the input and the expected results (the output), which can be copied directly into Excel cells.

Are there some instructions here on how to do that?

 

[Aladin Akyurek]

Please post a small sample, say G11:K15, the input and the expected results (the output), which can be copied directly into Excel cells.

Are there some instructions here on how to do that?

See: http://www.mrexcel.com/forum/about-board/508133-attachments.html and https://app.box.com/s/soezox25h3w0q5s4rcyl

 

[jericksonapga]

See: http://www.mrexcel.com/forum/about-board/508133-attachments.html and https://app.box.com/s/soezox25h3w0q5s4rcyl

Thanks. About 10 seconds after I posted I noticed I hadn't paid attention to the ** about saving the equation as an array equation -- that is a function of EXCEL I was not aware of. I'm reading up on it and that may have resolved my question.

Thanks for all the help -- I'll let you know how it works out.

 

[jericksonapga]

Thanks. About 10 seconds after I posted I noticed I hadn't paid attention to the ** about saving the equation as an array equation -- that is a function of EXCEL I was not aware of. I'm reading up on it and that may have resolved my question.

Thanks for all the help -- I'll let you know how it works out.

That worked -- THANKS!!!

 

[excelneedshelp]

The IF bit in

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

delivers column numbers of cells in A2:C5 which are equal to to the value of E2.

MAX picks out the highest column number and feeds that number to INDEX.

INDEX yields the value from A1:C1 at that column number.

For the sample the exhibit shows we get successively...

INDEX(A1:C1,MAX({FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,2,FALSE;FALSE,FALSE,FALSE}))

==>

=INDEX(A1:C1,2)

==>

Header2

Hope this helps.

Thanks for your help! However I still don't get the part on the [value_if_true] of the IF formula and what "-COLUMN(A1)+1" is supposed to mean. Do reply if you see this. Help would be greatly appreciated! Thanks!!

 

[Aladin Akyurek]

Thanks for your help! However I still don't get the part on the [value_if_true] of the IF formula and what "-COLUMN(A1)+1" is supposed to mean. Do reply if you see this. Help would be greatly appreciated! Thanks!!

The formula:

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

in which I replaced COLUMN(A2:C5) with COLUMN(A1:C1), not a change of significance.

What we want from the COLUMN(A1:C1)-COLUMN(A1)+1 bit, is that it consists of {1, 2, 3}. That's what happens:

{1,2,3}-COLUMN(A1)+1

>>

{1,2,3}-{1}+1

>>

{0,1,2}+1

>>

{1,2,3}

If we would insert a new column in front of column A in:

Row\Col	A​	B​	C​	D​	E​	F​
1​	Header1	Header2	Header3		Lookup	Location
2​	22​	10​	65​		94​	Header2
3​	44​	15​	59​
4​	48​	94​	31​
5​	94​	29​	86​

we would have:

COLUMN(B1:D1)-COLUMN(B1)+1

which would still yield exactly what we want:

{1,2,3}

That is,

COLUMN(A1:C1)

would not be robust for after the insertion of the column as described above, we would get:

COLUMN(B1:D1)

which would give:

{2,3,4}, not {1,2,3}.

Hope this is clear.

A side note. The main formula, as it is set up, would miss a second occurrence associated with a different header



Having only COLUMN(A1:C1) bit would