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

NairbNam

New Member
Joined
Dec 7, 2012
Messages
6
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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!
Try this...

Book1
ABCDEF
1Header1Header2Header3_LookupLocation
2221065_94Header2
3441559___
4489431___
5332986___
Sheet1

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.
 
Upvote 0
i want to do something similar but only in one row and instead of a max number i need to match a string. You couldn't help me out too, could you please?
 
Upvote 0
Try this...

Sheet1

ABCDEF
1Header1Header2Header3_LookupLocation
2221065_94Header2
3441559___
4489431___
5332986___

<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.


How about if my Lookup is "Header1" and the data i want to get is in cell A3 = 44 ... what formula should be?
 
Upvote 0
Unclear what you mean. Would you post a small sample along with the expected actual result?

Hi Aladin,

Below is the result i want..
ABCDEF
1Header1Header2Header3_Header1<<--Lookup Value
2221065_22<<-- Result
3441559_44<<-- Result
4489431_48<<-- Result
5332986_33<<-- Result

<tbody>
</tbody>
As every time i changed the Lookup Value (cell E1), the result will also change as to specified look up column.
Hope this will clarify of my query.

Thanks a lot.
 
Upvote 0
You could do it with the single formula in E2, copied down. However, to me it makes sense to use the method shown in columns G:H as the formulas are considerably simpler and the same calculation to determine the relevant column does not need to be continually repeated.

Excel Workbook
ABCDEFGH
1Header1Header2Header3Header2Header22
22210651010
34415591515
44894319494
53329862929
6
75544334444
8
Extract Column
 
Upvote 0
You could do it with the single formula in E2, copied down. However, to me it makes sense to use the method shown in columns G:H as the formulas are considerably simpler and the same calculation to determine the relevant column does not need to be continually repeated.

Extract Column

ABCDEFGH
1Header1Header2Header3 Header2 Header22
2221065 10 10
3441559 15 15
4489431 94 94
5332986 29 29
6
7554433 44 44
8

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

Spreadsheet Formulas
CellFormula
H1=MATCH(G1,A1:C1,0)
E2=IF(INDEX(A$2:C$10,ROWS(E$2:E2),MATCH(E$1,A$1:C$1,0))="","",INDEX(A$2:C$10,ROWS(E$2:E2),MATCH(E$1,A$1:C$1,0)))
G2=IF(INDEX(A2:C2,H$1)="","",INDEX(A2:C2,H$1))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Thank you so much Peter! It Works!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top