How do I get Excel to return the column header of text that appears in an array?

[PCnetMD]

I need to determine which column a specific item is located.

1	2	3	4
water	lettuce	chicken	ice cream
cola	tomato	beef	cake
juice	cucumber	pork	pie
coffee	okra	lamb	brownie
tea	carrot	shrimp	cookie
milk	onion	fish	candy

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

How do I get Excel to return the column header of text that appears in the above array? Everything I have found only works for number(values).

Thank you,

 

[shaowu459]

I need to determine which column a specific item is located.

1	2	3	4
water	lettuce	chicken	ice cream
cola	tomato	beef	cake
juice	cucumber	pork	pie
coffee	okra	lamb	brownie
tea	carrot	shrimp	cookie
milk	onion	fish	candy

<tbody>
</tbody>

How do I get Excel to return the column header of text that appears in the above array? Everything I have found only works for number(values).

Thank you,

=INDEX(A1:D1,MAX((A2:D7="pork")*COLUMN(A:D)))
Ctrl shift enter for the array formula.

 

[alansidman]

Assume your data is arranged as follows:

Data Range

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	water​					1​	2​	3​	4​
2​	cola​					water​	lettuce​	chicken​	ice cream​
3​	juice​					cola​	tomato​	beef​	cake​
4​	coffee​					juice​	cucumber​	pork​	pie​
5​	tea​					coffee​	okra​	lamb​	brownie​
6​	milk​					tea​	carrot​	shrimp​	cookie​
7​	lettuce​					milk​	onion​	fish​	candy​
8​	tomato​
9​	cucumber​
10​	okra​
11​	carrot​
12​	onion​
13​	chicken​
14​	beef​
15​	pork​
16​	lamb​
17​	shrimp​
18​	fish​
19​	ice cream​
20​	cake​
21​	pie​
22​	brownie​
23​	cookie​
24​	candy​

<tbody>
</tbody>

then the following VBA will do what you need

Option Explicit


Sub foo()
    Dim i As Long, c As Range
    Dim rng As Range, x As Long
    Set rng = Range("F2:I7")
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        For Each c In rng
            If Range("A" & i) = c Then
                x = c.Column
                Range("B" & i) = Cells(1, x)
            End If
        Next c
    Next i
End Sub

 

[harshshastri]

Use this formula

=SUMPRODUCT((a1:d6="tea")*(COLUMN(a1:d6)))

you will get answer as 1

 

[Aladin Akyurek]

Does a value like okra repeat? If it does, do you need all of the corresponding headers? By the way, is the set of 1, 2, 3, and 4 the header set?