match formula
 

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

match formula
 

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:
=SUBSTITUTE(ADDRESS(1,SUMPRODUCT((rng=N1)*(COLUMN( rng))),4),"1","")

the address:
=ADDRESS(SUMPRODUCT((rng=N1)*(ROW(rng))),SUMPRODUC T((rng=N1)*(COLUMN(rng))))

the value:
=INDIRECT(ADDRESS(SUMPRODUCT((rng=N1)*(ROW(rng))), SUMPRODUCT((rng=N1)*(COLUMN(rng)))))

--
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.)

TIA for any ideas.
Pete

match formula
 

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(C OUNTIF($C11:$G11,$N$1:$N$8)0,ROW($N$1:$N$8)-ROW($N$1)+1),ROWS($H11:H11))),"BOOK PRICE")))}

Thanks again.
Pete
On Thursday, June 21, 2012 11:26:49 AM UTC-5, isabelle wrote:
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:
=SUBSTITUTE(ADDRESS(1,SUMPRODUCT((rng=N1)*(COLUMN( rng))),4),"1","")

the address:
=ADDRESS(SUMPRODUCT((rng=N1)*(ROW(rng))),SUMPRODUC T((rng=N1)*(COLUMN(rng))))

the value:
=INDIRECT(ADDRESS(SUMPRODUCT((rng=N1)*(ROW(rng))), SUMPRODUCT((rng=N1)*(COLUMN(rng)))))

--
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.)

TIA for any ideas.
Pete