Thanks:  0
Likes:  0

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

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

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!

2. ## Re: Finding a value in a multiple column array and returning column header - Excel 2003

Originally Posted by 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!
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 _ _ _

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.

3. ## Re: Finding a value in a multiple column array and returning column header - Excel 2003

Perfect! This is exactly what I was looking for, thank you.

4. ## Re: Finding a value in a multiple column array and returning column header - Excel 2003

Originally Posted by NairbNam
Perfect! This is exactly what I was looking for, thank you.
You're welcome. Thanks for the feedback!

5. ## Re: Finding a value in a multiple column array and returning column header - Excel 2003

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?

6. ## Re: Finding a value in a multiple column array and returning column header - Excel 2003

Originally Posted by T. Valko
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 _ _ _

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?

7. ## Re: Finding a value in a multiple column array and returning column header - Excel 2003

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

8. ## Re: Finding a value in a multiple column array and returning column header - Excel 2003

Unclear what you mean. Would you post a small sample along with the expected actual result?

Below is the result i want..
 A B C D E F 1 Header1 Header2 Header3 _ Header1 <<--Lookup Value 2 22 10 65 _ 22 <<-- Result 3 44 15 59 _ 44 <<-- Result 4 48 94 31 _ 48 <<-- Result 5 33 29 86 _ 33 <<-- Result
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.

9. ## Re: Finding a value in a multiple column array and returning column header - Excel 2003

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

 A B C D E F G H 1 Header1 Header2 Header3 Header2 Header2 2 2 22 10 65 10 10 3 44 15 59 15 15 4 48 94 31 94 94 5 33 29 86 29 29 6 7 55 44 33 44 44 8

 Cell Formula 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))

Excel tables to the web >> Excel Jeanie HTML 4

10. ## Re: Finding a value in a multiple column array and returning column header - Excel 2003

Originally Posted by Peter_SSs
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

 A B C D E F G H 1 Header1 Header2 Header3 Header2 Header2 2 2 22 10 65 10 10 3 44 15 59 15 15 4 48 94 31 94 94 5 33 29 86 29 29 6 7 55 44 33 44 44 8

 Cell Formula 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))

Excel tables to the web >> Excel Jeanie HTML 4

Thank you so much Peter! It Works!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•