| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula to return exact match
The following looks in Uses! A:A to find a match for B189 and then looks in C
to find and return value, if present. IF(B189="","",IF(ISNA(VLOOKUP(B189,Uses!A:C,3,0)), "",VLOOKUP(B189,Uses!A:C,3,0))) I want now to return a particular value located in C:C Because there is now more than one possible matching record in Uses! A:A to match B189 and there may also be more than one non duplicate values in C:C the formula need to be able to know what value to return. To explain this better here is an example: B189 = €śToyota€ť €śToyota€ť can be found in Uses!A41, A42, A43,A44 In Uses!C:C some cells contain values, these are non duplicate values for !Toyota€ť Uses!C41 = hatchback Uses!C42 = salon Uses!C43 = Uses!C44 = compact If I just want to return €ścompact€ť what way does the formula need to be modified to give this result. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula to return exact match
If you mean to return the last matching entry try the below. Please note that
this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results.You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(uses!C1:C1000,LARGE(IF(uses!A1:A1000=B189,R OW(uses!A1:A1000)),1)) If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: The following looks in Uses! A:A to find a match for B189 and then looks in C to find and return value, if present. IF(B189="","",IF(ISNA(VLOOKUP(B189,Uses!A:C,3,0)), "",VLOOKUP(B189,Uses!A:C,3,0))) I want now to return a particular value located in C:C Because there is now more than one possible matching record in Uses! A:A to match B189 and there may also be more than one non duplicate values in C:C the formula need to be able to know what value to return. To explain this better here is an example: B189 = €śToyota€ť €śToyota€ť can be found in Uses!A41, A42, A43,A44 In Uses!C:C some cells contain values, these are non duplicate values for !Toyota€ť Uses!C41 = hatchback Uses!C42 = salon Uses!C43 = Uses!C44 = compact If I just want to return €ścompact€ť what way does the formula need to be modified to give this result. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula to return exact match
gootroots wrote:
The following looks in Uses! A:A to find a match for B189 and then looks in C to find and return value, if present. IF(B189="","",IF(ISNA(VLOOKUP(B189,Uses!A:C,3,0)), "",VLOOKUP(B189,Uses!A:C,3,0))) I want now to return a particular value located in C:C Because there is now more than one possible matching record in Uses! A:A to match B189 and there may also be more than one non duplicate values in C:C the formula need to be able to know what value to return. To explain this better here is an example: B189 = €śToyota€ť €śToyota€ť can be found in Uses!A41, A42, A43,A44 In Uses!C:C some cells contain values, these are non duplicate values for !Toyota€ť Uses!C41 = hatchback Uses!C42 = salon Uses!C43 = Uses!C44 = compact If I just want to return €ścompact€ť what way does the formula need to be modified to give this result. Explain why "compact" is the desired result. The last match, the first match after a blank, the first match alphabetically... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula to return exact match
Hi Jacob Skaria
If you mean to return the last matching entry try the below. actually I do want to return the last matching entry but be able to return any one of the entries at random. "Jacob Skaria" wrote: If you mean to return the last matching entry try the below. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results.You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(uses!C1:C1000,LARGE(IF(uses!A1:A1000=B189,R OW(uses!A1:A1000)),1)) If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: The following looks in Uses! A:A to find a match for B189 and then looks in C to find and return value, if present. IF(B189="","",IF(ISNA(VLOOKUP(B189,Uses!A:C,3,0)), "",VLOOKUP(B189,Uses!A:C,3,0))) I want now to return a particular value located in C:C Because there is now more than one possible matching record in Uses! A:A to match B189 and there may also be more than one non duplicate values in C:C the formula need to be able to know what value to return. To explain this better here is an example: B189 = €śToyota€ť €śToyota€ť can be found in Uses!A41, A42, A43,A44 In Uses!C:C some cells contain values, these are non duplicate values for !Toyota€ť Uses!C41 = hatchback Uses!C42 = salon Uses!C43 = Uses!C44 = compact If I just want to return €ścompact€ť what way does the formula need to be modified to give this result. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula to return exact match
Try the below array formula..
=INDEX(uses!C1:C1000,SMALL(IF(uses!A1:A1000=B189, ROW(uses!A1:A1000)),RANDBETWEEN(1,COUNTIF(uses!A1: A1000,B189)))) If you are using 2003 and if the formula returns #VALUE! error then enable AnalysisToolPak from Menu ToolsAddInscheck 'Analysis tool Pak' OK If this post helps click Yes --------------- Jacob Skaria "Gotroots" wrote: Hi Jacob Skaria If you mean to return the last matching entry try the below. actually I do want to return the last matching entry but be able to return any one of the entries at random. "Jacob Skaria" wrote: If you mean to return the last matching entry try the below. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results.You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(uses!C1:C1000,LARGE(IF(uses!A1:A1000=B189,R OW(uses!A1:A1000)),1)) If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: The following looks in Uses! A:A to find a match for B189 and then looks in C to find and return value, if present. IF(B189="","",IF(ISNA(VLOOKUP(B189,Uses!A:C,3,0)), "",VLOOKUP(B189,Uses!A:C,3,0))) I want now to return a particular value located in C:C Because there is now more than one possible matching record in Uses! A:A to match B189 and there may also be more than one non duplicate values in C:C the formula need to be able to know what value to return. To explain this better here is an example: B189 = €śToyota€ť €śToyota€ť can be found in Uses!A41, A42, A43,A44 In Uses!C:C some cells contain values, these are non duplicate values for !Toyota€ť Uses!C41 = hatchback Uses!C42 = salon Uses!C43 = Uses!C44 = compact If I just want to return €ścompact€ť what way does the formula need to be modified to give this result. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula to return exact match
Although a highly impressive formula this is not what is needed.
Somehere in the formula I need to specify the value I want returned ie. "hatchback" "salon" "compact" or indeed any value whether found in Uses!C:C or not. "Jacob Skaria" wrote: Try the below array formula.. =INDEX(uses!C1:C1000,SMALL(IF(uses!A1:A1000=B189, ROW(uses!A1:A1000)),RANDBETWEEN(1,COUNTIF(uses!A1: A1000,B189)))) If you are using 2003 and if the formula returns #VALUE! error then enable AnalysisToolPak from Menu ToolsAddInscheck 'Analysis tool Pak' OK If this post helps click Yes --------------- Jacob Skaria "Gotroots" wrote: Hi Jacob Skaria If you mean to return the last matching entry try the below. actually I do want to return the last matching entry but be able to return any one of the entries at random. "Jacob Skaria" wrote: If you mean to return the last matching entry try the below. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results.You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(uses!C1:C1000,LARGE(IF(uses!A1:A1000=B189,R OW(uses!A1:A1000)),1)) If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: The following looks in Uses! A:A to find a match for B189 and then looks in C to find and return value, if present. IF(B189="","",IF(ISNA(VLOOKUP(B189,Uses!A:C,3,0)), "",VLOOKUP(B189,Uses!A:C,3,0))) I want now to return a particular value located in C:C Because there is now more than one possible matching record in Uses! A:A to match B189 and there may also be more than one non duplicate values in C:C the formula need to be able to know what value to return. To explain this better here is an example: B189 = €śToyota€ť €śToyota€ť can be found in Uses!A41, A42, A43,A44 In Uses!C:C some cells contain values, these are non duplicate values for !Toyota€ť Uses!C41 = hatchback Uses!C42 = salon Uses!C43 = Uses!C44 = compact If I just want to return €ścompact€ť what way does the formula need to be modified to give this result. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula to return exact match
Glenn
Please see my latest post. "Glenn" wrote: gootroots wrote: The following looks in Uses! A:A to find a match for B189 and then looks in C to find and return value, if present. IF(B189="","",IF(ISNA(VLOOKUP(B189,Uses!A:C,3,0)), "",VLOOKUP(B189,Uses!A:C,3,0))) I want now to return a particular value located in C:C Because there is now more than one possible matching record in Uses! A:A to match B189 and there may also be more than one non duplicate values in C:C the formula need to be able to know what value to return. To explain this better here is an example: B189 = €śToyota€ť €śToyota€ť can be found in Uses!A41, A42, A43,A44 In Uses!C:C some cells contain values, these are non duplicate values for !Toyota€ť Uses!C41 = hatchback Uses!C42 = salon Uses!C43 = Uses!C44 = compact If I just want to return €ścompact€ť what way does the formula need to be modified to give this result. Explain why "compact" is the desired result. The last match, the first match after a blank, the first match alphabetically... . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula to return exact match
Array formula:
=IF(SUM(--(uses!A1:A1000&uses!C1:C1000=B189&"compact")),"com pact","") Gotroots wrote: Although a highly impressive formula this is not what is needed. Somehere in the formula I need to specify the value I want returned ie. "hatchback" "salon" "compact" or indeed any value whether found in Uses!C:C or not. "Jacob Skaria" wrote: Try the below array formula.. =INDEX(uses!C1:C1000,SMALL(IF(uses!A1:A1000=B189, ROW(uses!A1:A1000)),RANDBETWEEN(1,COUNTIF(uses!A1: A1000,B189)))) If you are using 2003 and if the formula returns #VALUE! error then enable AnalysisToolPak from Menu ToolsAddInscheck 'Analysis tool Pak' OK If this post helps click Yes --------------- Jacob Skaria "Gotroots" wrote: Hi Jacob Skaria If you mean to return the last matching entry try the below. actually I do want to return the last matching entry but be able to return any one of the entries at random. "Jacob Skaria" wrote: If you mean to return the last matching entry try the below. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results.You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(uses!C1:C1000,LARGE(IF(uses!A1:A1000=B189,R OW(uses!A1:A1000)),1)) If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: The following looks in Uses! A:A to find a match for B189 and then looks in C to find and return value, if present. IF(B189="","",IF(ISNA(VLOOKUP(B189,Uses!A:C,3,0)), "",VLOOKUP(B189,Uses!A:C,3,0))) I want now to return a particular value located in C:C Because there is now more than one possible matching record in Uses! A:A to match B189 and there may also be more than one non duplicate values in C:C the formula need to be able to know what value to return. To explain this better here is an example: B189 = €śToyota€ť €śToyota€ť can be found in Uses!A41, A42, A43,A44 In Uses!C:C some cells contain values, these are non duplicate values for !Toyota€ť Uses!C41 = hatchback Uses!C42 = salon Uses!C43 = Uses!C44 = compact If I just want to return €ścompact€ť what way does the formula need to be modified to give this result. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula to return exact match
Glenn
You have nailed it Job well done! From one very grateful excel user. "Glenn" wrote: Array formula: =IF(SUM(--(uses!A1:A1000&uses!C1:C1000=B189&"compact")),"com pact","") Gotroots wrote: Although a highly impressive formula this is not what is needed. Somehere in the formula I need to specify the value I want returned ie. "hatchback" "salon" "compact" or indeed any value whether found in Uses!C:C or not. "Jacob Skaria" wrote: Try the below array formula.. =INDEX(uses!C1:C1000,SMALL(IF(uses!A1:A1000=B189, ROW(uses!A1:A1000)),RANDBETWEEN(1,COUNTIF(uses!A1: A1000,B189)))) If you are using 2003 and if the formula returns #VALUE! error then enable AnalysisToolPak from Menu ToolsAddInscheck 'Analysis tool Pak' OK If this post helps click Yes --------------- Jacob Skaria "Gotroots" wrote: Hi Jacob Skaria If you mean to return the last matching entry try the below. actually I do want to return the last matching entry but be able to return any one of the entries at random. "Jacob Skaria" wrote: If you mean to return the last matching entry try the below. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results.You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(uses!C1:C1000,LARGE(IF(uses!A1:A1000=B189,R OW(uses!A1:A1000)),1)) If this post helps click Yes --------------- Jacob Skaria "gootroots" wrote: The following looks in Uses! A:A to find a match for B189 and then looks in C to find and return value, if present. IF(B189="","",IF(ISNA(VLOOKUP(B189,Uses!A:C,3,0)), "",VLOOKUP(B189,Uses!A:C,3,0))) I want now to return a particular value located in C:C Because there is now more than one possible matching record in Uses! A:A to match B189 and there may also be more than one non duplicate values in C:C the formula need to be able to know what value to return. To explain this better here is an example: B189 = €śToyota€ť €śToyota€ť can be found in Uses!A41, A42, A43,A44 In Uses!C:C some cells contain values, these are non duplicate values for !Toyota€ť Uses!C41 = hatchback Uses!C42 = salon Uses!C43 = Uses!C44 = compact If I just want to return €ścompact€ť what way does the formula need to be modified to give this result. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IINDEX and MATCH formula with more than one return value | Excel Discussion (Misc queries) | |||
| Find an exact match and go to that match | Excel Discussion (Misc queries) | |||
| Need a function to return EXACT row number of a match | Excel Worksheet Functions | |||
| Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
| Linking sheet - Formula Match Exact or select next row and cell | Excel Discussion (Misc queries) | |||
Linear Mode
