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]

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]

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.

Yes, Sir! That worked. Thanks for sticking with me. Appreciate it.

 

[Peter_SSs]

If you did want an option without the need for Ctrl+Shift+Enter (which would be required again if you edit the formula, say to change the ranges) you could try this one.

24 01 15.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
topi1

Cell Formulas
Range	Formula
B3:B7	B3	=INDEX($1:$1,AGGREGATE(15,6,COLUMN(D:F)/(D$2:F$6=A3),1))

 

[topi1]

If you did want an option without the need for Ctrl+Shift+Enter (which would be required again if you edit the formula, say to change the ranges) you could try this one.

24 01 15.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
topi1

Cell Formulas
Range	Formula
B3:B7	B3	=INDEX($1:$1,AGGREGATE(15,6,COLUMN(D:F)/(D$2:F$6=A3),1))

That works very well! I appreciate it greatly.

 

[Peter_SSs]

Cheers. Glad to contribute.