Match with multiple columns

[topi1]

In the following example, I have a list of fruits and vegetables in Column A. Columns D and E have names of few fruits and vegetables. In the column B, I have entered countif formula to see if the object in the column A is a fruit or vegetable. The formula works but in real life I have numerous columns and not just D & E, and I don't know how to write something which will look at each of those columns and then get the answer which will be equal to the first row of the matched column. B will be blank if unmatched. Thank you in advance.

Book2
	A	B	C	D	E
1				Vegetable	Fruit
2				Carrots	Grapes
3	Broccoli	Vegetable		Broccoli	Banana
4	Orange	Fruit		Spinach	Orange
5	Spinach	Vegetable		cucumbers	Apple
6	Apple	Fruit
7
8
9
10
11
12
13
14
15
Sheet1

Cell Formulas
Range	Formula
B3:B6	B3	=IF((COUNTIF($D$2:$D$15,$A3)=1),$D$1,IF((COUNTIF($E$2:$E$15,$A3)=1),$E$1,""))

 

[DanteAmor]

Try

varios 13ene2024.xlsm
	A	B	C	D	E	F
1				Vegetable	Fruit	Liquid
2				Carrots	Grapes	Vino
3	Broccoli	Vegetable		Broccoli	Banana
4	Orange	Fruit		Spinach	Orange
5	Spinach	Vegetable		cucumbers	Apple
6	Apple	Fruit
7	Vino	Liquid
Hoja6

Cell Formulas
Range	Formula
B3:B7	B3	=INDEX($D$1:$F$1,SUMPRODUCT(($D$2:$F$5=A3)*COLUMN($D$1:$F$1))-COLUMN($D$1)+1)

 

[topi1]

Fantastic, Thank you so much for your prompt help. For now, I extended data range to more rows and columns as follows.

INDEX($D$1:$ZZ$1,SUMPRODUCT(($D$2:$ZZ$500=A3)*COLUMN($D$1:$ZZ$1))-COLUMN($D$1)+1)

Thank you again.

 

[topi1]

@DanteAmor didn't know about the check mark as a solution feature. Market your answer as such.

 

[topi1]

@DanteAmor Can you please modify the formula so it will not give "0" as a result if the item in the D onwards column is duplicated. For example, spinach is in D4. If I add spinach in D6, B5 becomes "0". I would like the formula to ignore duplicates and yield "vegetables" even if D4 and D6 have spinach. Thank you.

 

[DanteAmor]

For example, spinach is in D4. If I add spinach in D6

Try:

Dante Amor
	A	B	C	D	E	F
1				Vegetable	Fruit	Liquid
2				Carrots	Grapes	Vino
3	Broccoli	Vegetable		Broccoli	Banana
4	Orange	Fruit		Spinach	Orange
5	Spinach	Vegetable		cucumbers	Apple
6	Apple	Fruit		Spinach
7	Vino	Liquid
Hoja6

Cell Formulas
Range	Formula
B3:B7	B3	=INDEX($D$1:$F$1,MAX(($D$2:$F$8=A3)*(COLUMN(D1:F1)))-COLUMN($D$1)+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

That is an array formula, to enter it, you must edit it and press the 3 keys Shift + Control + Enter (Not just Enter)
If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

 

[topi1]

Thank you @DanteAmor . I am getting Vegetables in B3 to B7.

Book3
	A	B	C	D	E	F
1				Vegetable	Fruit	Liquid
2				Carrots	Grapes	Vino
3	Broccoli	Vegetable		Broccoli	Banana
4	Orange	Vegetable		Spinach	Orange
5	Spinach	Vegetable		cucumbers	Apple
6	Apple	Vegetable		Spinach
7	Vino	Vegetable
Sheet2

Cell Formulas
Range	Formula
B3:B7	B3	=INDEX($D$1:$F$1,MAX(($D$2:$F$8=A3)*(COLUMN(D1:F1)))-COLUMN($D$1)+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DanteAmor]

My fault.
The absolute reference was missing in $D$1:$F$1

varios 13ene2024b.xlsm
	A	B	C	D	E	F
1				Vegetable	Fruit	Liquid
2				Carrots	Grapes	Vino
3	Broccoli	Vegetable		Broccoli	Banana
4	Orange	Fruit		Spinach	Orange
5	Spinach	Vegetable		cucumbers	Apple
6	Apple	Fruit		Spinach
7	Vino	Liquid
Hoja6

Cell Formulas
Range	Formula
B3:B7	B3	=INDEX($D$1:$F$1,MAX(($D$2:$F$8=A3)*(COLUMN($D$1:$F$1)))-COLUMN($D$1)+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

No, that's not the problem.

The problem is that you must put the formula in cell B3, edit it, press the 3 keys Shift + Control + Enter and then copy the formula down.
It seems you are selecting all cells B3 to B7 and then pressing the 3 keys and that is not how it should be.

 

[topi1]

Sorry to keep bothering you but it did not help.

Book3
	A	B	C	D	E	F
1				Vegetable	Fruit	Liquid
2				Carrots	Grapes	Vino
3	Broccoli	Vegetable		Broccoli	Banana
4	Orange	Vegetable		Spinach	Orange
5	Spinach	Vegetable		cucumbers	Apple
6	Apple	Vegetable		Spinach
7	Vino	Vegetable
Sheet2

Cell Formulas
Range	Formula
B3:B7	B3	=INDEX($D$1:$F$1,MAX(($D$2:$F$8=A3)*(COLUMN($D$1:$F$1)))-COLUMN($D$1)+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[topi1]

@DanteAmor tried again after rebooting. Even tried without any repeats. Did not work. Any help would be greatly appreciated.