retrieving column number out of a sheet

[Gold_fingers_95]

Hello everyone,
I have listed car brands in a sheet and distributed them on several columns. (as follows)

[TABLE="width: 384"]
<tbody>[TR]
[TD][/TD]
[TD]A10[/TD]
[TD]A11[/TD]
[TD]A12[/TD]
[TD]A13[/TD]
[TD]A14[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]GRADE 1[/TD]
[TD]GRADE 2[/TD]
[TD]GRADE 3[/TD]
[TD]GRADE 4[/TD]
[TD]GRADE 5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Lexus[/TD]
[TD]Chevrolet[/TD]
[TD]Audi[/TD]
[TD]Alfa Romeo[/TD]
[TD]Chery[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Nissan[/TD]
[TD]Dodge[/TD]
[TD]BMW[/TD]
[TD]Citroen[/TD]
[TD]Chrysler[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mitsubishi[/TD]
[TD]GMC[/TD]
[TD]Cadillac[/TD]
[TD]Daihatsu[/TD]
[TD]DongFeng[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Suzuki[/TD]
[TD]Honda[/TD]
[TD]Ford[/TD]
[TD]Hyundai[/TD]
[TD]Fiat[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Toyota[/TD]
[TD][/TD]
[TD]Hummer[/TD]
[TD]Jaguar[/TD]
[TD]Geely[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Infiniti[/TD]
[TD]Jeep[/TD]
[TD]Isuzu[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]Land Rover[/TD]
[TD]Kia[/TD]
[TD]Lancia[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mercedes-Benz[/TD]
[TD]Mazda[/TD]
[TD]MINI[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Porsche[/TD]
[TD]Peugeot[/TD]
[TD]Saab[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]Renault[/TD]
[TD]SsangYong[/TD]
[TD]Volvo[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Volkswagen[/TD]
[TD]Subaru[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My concern is to get in a cell the "Grade" in which the brand exists. (e.g if I type "Dodge" in a specific cell, I should get GRADE 2 or the concerned column number "2")

Thanks in advance guys

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]**GRADE 1[/td][td]**GRADE 2[/td][td]**GRADE 3[/td][td]**GRADE 4[/td][td]**GRADE 5[/td][td][/td][td]**Dodge[/td][/tr]

[tr][td]

2​

[/td][td]**Lexus[/td][td]**Chevrolet[/td][td]**Audi[/td][td]**Alfa Romeo[/td][td]**Chery[/td][td][/td][td]GRADE 2[/td][/tr]

[tr][td]

3​

[/td][td]**Nissan[/td][td]**Dodge[/td][td]**BMW[/td][td]**Citroen[/td][td]**Chrysler[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]**Mitsubishi[/td][td]**GMC[/td][td]**Cadillac[/td][td]**Daihatsu[/td][td]**DongFeng[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]**Suzuki[/td][td]**Honda[/td][td]**Ford[/td][td]**Hyundai[/td][td]**Fiat[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]**Toyota[/td][td]**[/td][td]**Hummer[/td][td]**Jaguar[/td][td]**Geely[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]**[/td][td]**[/td][td]**Infiniti[/td][td]**Jeep[/td][td]**Isuzu[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]**[/td][td]**[/td][td]**Land Rover[/td][td]**Kia[/td][td]**Lancia[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]**[/td][td]**[/td][td]**Mercedes-Benz[/td][td]**Mazda[/td][td]**MINI[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]**[/td][td]**[/td][td]**Porsche[/td][td]**Peugeot[/td][td]**Saab[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]**[/td][td]**[/td][td]**Renault[/td][td]**SsangYong[/td][td]**Volvo[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]**[/td][td]**[/td][td]**Volkswagen[/td][td]**Subaru[/td][td][/td][td][/td][td][/td][/tr]
[/table]

G2, control+shift+enter, not just enter:

=IFERROR(INDEX($A$1:$E$1,1/(1/MAX(IF($A$2:$E$12=G1,COLUMN($A$1:$E$1)-COLUMN($A$1)+1)))),"")

 

[Gold_fingers_95]

Thank you for the quick reply! It's just... ...perfect :D
Just for info, what type of function is that?

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]**GRADE 1[/TD]
[TD]**GRADE 2[/TD]
[TD]**GRADE 3[/TD]
[TD]**GRADE 4[/TD]
[TD]**GRADE 5[/TD]
[TD][/TD]
[TD]**Dodge[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]**Lexus[/TD]
[TD]**Chevrolet[/TD]
[TD]**Audi[/TD]
[TD]**Alfa Romeo[/TD]
[TD]**Chery[/TD]
[TD][/TD]
[TD]GRADE 2[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]**Nissan[/TD]
[TD]**Dodge[/TD]
[TD]**BMW[/TD]
[TD]**Citroen[/TD]
[TD]**Chrysler[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]**Mitsubishi[/TD]
[TD]**GMC[/TD]
[TD]**Cadillac[/TD]
[TD]**Daihatsu[/TD]
[TD]**DongFeng[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]**Suzuki[/TD]
[TD]**Honda[/TD]
[TD]**Ford[/TD]
[TD]**Hyundai[/TD]
[TD]**Fiat[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]**Toyota[/TD]
[TD]**[/TD]
[TD]**Hummer[/TD]
[TD]**Jaguar[/TD]
[TD]**Geely[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**Infiniti[/TD]
[TD]**Jeep[/TD]
[TD]**Isuzu[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**Land Rover[/TD]
[TD]**Kia[/TD]
[TD]**Lancia[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**Mercedes-Benz[/TD]
[TD]**Mazda[/TD]
[TD]**MINI[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**Porsche[/TD]
[TD]**Peugeot[/TD]
[TD]**Saab[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**Renault[/TD]
[TD]**SsangYong[/TD]
[TD]**Volvo[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**Volkswagen[/TD]
[TD]**Subaru[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


G2, control+shift+enter, not just enter:

=IFERROR(INDEX($A$1:$E$1,1/(1/MAX(IF($A$2:$E$12=G1,COLUMN($A$1:$E$1)-COLUMN($A$1)+1)))),"")

 

[Aladin Akyurek]

Thank you for the quick reply! It's just... ...perfect :D
Just for info, what type of function is that?

MAX would return 0 when it fails,i.e., when what it looks for is not available. Correlating 0 with the range INDEX is referencing would deliver the first element of that range. Such is of course obviously false. Using:

1/(1/MAX(...))

we get #DIV/0! if MAX fails or we get the MAX value back:

Suppose MAX = 4.

1/(1/(4))

==> 4

Thus: MAX = 0 ==> #DIV/0! or MAX = n ==> n.

Feeding the result to IFERROR allows using the foregoing relational feature.

 

[Rick Rothstein]

Just for info, what type of function is that?

I read your question differently than Aladin did. I think you were asking about formulas that are committed with CTRL+SHIFT+ENTER, so I'll answer that one. They are called "array formulas" and here is a link that will give you more information about them...

Array Formulas