Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match formula to match values in multiple columns | Excel Discussion (Misc queries) | |||
Match formula to match values in multiple columns | Excel Programming | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions |