# match formula

P

#### Pete

Am using this formula:

=IF(ISNA(MATCH(\$N\$1:\$N\$8,\$C\$9:\$G\$9,0)),"Y","N")

to review columns C through G to determine if any of the columns contain a value found in the range \$N\$1:\$N\$8

It works fine, but what I'd like the formula to return is the actual value that was matched. (From there, I could do a lookup against the result, and pull in another value.)

TIA for any ideas.
Pete

I

#### isabelle

hi Pete,

you must search each of the valuesâ€‹â€‹, one by one
i named "rng" the range \$C\$9:\$B\$9

the row:
=SUMPRODUCT((rng=N1)*(ROW(rng)))

the column:

the value:

--
isabelle

Le 2012-06-18 12:44, Pete a Ã©crit :
Am using this formula:

=IF(ISNA(MATCH(\$N\$1:\$N\$8,\$C\$9:\$G\$9,0)),"Y","N")

to review columns C through G to determine if any of the columns contain a value found in the range \$N\$1:\$N\$8

It works fine, but what I'd like the formula to return is the actual value that was matched.

(From there, I could do a lookup against the result, and pull in another value.)

P

#### Pete

Thank you Isabelle for your thoughts, time & effort. Your method worked just dandy.

. ., and after continuing to poke about the forums; this formula also did the trick:
{=IF(A11="","",(IFERROR(INDEX(\$N\$1:\$N\$8,SMALL(IF(COUNTIF(\$C11:\$G11,\$N\$1:\$N\$8)>0,ROW(\$N\$1:\$N\$8)-ROW(\$N\$1)+1),ROWS(\$H11:H11))),"BOOK PRICE")))}

Thanks again.
Pete