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