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)

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

<tbody>
</tbody>

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]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	**GRADE 1	**GRADE 2	**GRADE 3	**GRADE 4	**GRADE 5		**Dodge
2​	**Lexus	**Chevrolet	**Audi	**Alfa Romeo	**Chery		GRADE 2
3​	**Nissan	**Dodge	**BMW	**Citroen	**Chrysler
4​	**Mitsubishi	**GMC	**Cadillac	**Daihatsu	**DongFeng
5​	**Suzuki	**Honda	**Ford	**Hyundai	**Fiat
6​	**Toyota	**	**Hummer	**Jaguar	**Geely
7​	**	**	**Infiniti	**Jeep	**Isuzu
8​	**	**	**Land Rover	**Kia	**Lancia
9​	**	**	**Mercedes-Benz	**Mazda	**MINI
10​	**	**	**Porsche	**Peugeot	**Saab
11​	**	**	**Renault	**SsangYong	**Volvo
12​	**	**	**Volkswagen	**Subaru

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?

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	**GRADE 1	**GRADE 2	**GRADE 3	**GRADE 4	**GRADE 5		**Dodge
2​	**Lexus	**Chevrolet	**Audi	**Alfa Romeo	**Chery		GRADE 2
3​	**Nissan	**Dodge	**BMW	**Citroen	**Chrysler
4​	**Mitsubishi	**GMC	**Cadillac	**Daihatsu	**DongFeng
5​	**Suzuki	**Honda	**Ford	**Hyundai	**Fiat
6​	**Toyota	**	**Hummer	**Jaguar	**Geely
7​	**	**	**Infiniti	**Jeep	**Isuzu
8​	**	**	**Land Rover	**Kia	**Lancia
9​	**	**	**Mercedes-Benz	**Mazda	**MINI
10​	**	**	**Porsche	**Peugeot	**Saab
11​	**	**	**Renault	**SsangYong	**Volvo
12​	**	**	**Volkswagen	**Subaru

<tbody>
</tbody>

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