MATCH with multiple columns

[ub92]

I have two questions and I haven't found any answers in my searching. One, I think, is very simple but I am just not finding it. I will save the second question, which is more complicated, for later.

Here's my question:

I have attached a sample Excel file.

In column A I have several three-digit numbers.

I would like to search columns D, E and F for each of the entries in column A. If the entry from column A is found in one of those columns, I would like to put the column heading (apples, pears or gumbo) in column B (in the cell next to the three-digit number in column A).

How would I do that? I assume some form of MATCH, but I can not get it to work for multiple columns. At any rate, I suspect this is bit of a softball and I thank you in advance for helping me out with this.

Best regards,

Don

[Ghozi Alkatiri]

(array formula)start at B2
{=INDEX($A$1:$F$1;MIN(IF($D$2:$F$13=A2;COLUMN($D$2:$F$13))))}
copy down

[FDibbins]

some1 will probably give you a neater, more elegant solution, but until then, try this,,,

=IF(ISERROR(MATCH(A2,$D$2:$D$13,0)),IF(ISERROR(MATCH(A2,$E$2:$E$13,0)),IF(ISERROR(MATCH(A2,$F$2:$F$13,0)),"",$F$1),$E$1),D$1)

[Ace_XL]

Try..

Please Login or Register  to view this content.

[benishiryo]

hi Don, welcome to the forum. 2 questions.
1. what if the numbers cannot be found in the table? for eg, "123". my formula shows as blank
2. what if there are repeats in the 3 columns?

here's what i have so far:
=IF(COUNTIF($D$2:$F$13,A2)=0,"",INDEX($D$1:$F$1,MAX(IF($D$2:$F$13=A2,COLUMN($D$2:$F$13)-3))))

you must paste this formula inside the formula bar of B2, & press CTRL + SHIFT + ENTER to confirm

[kvsrinivasamurthy]

PL See the attached file with formula in B column.

[ub92]

Thanks...I have marked as Solved.