Need formula to return exact match

  • Thread starter Thread starter gootroots
  • Start date Start date
G

gootroots

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.
 
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,ROW(uses!A1:A1000)),1))

If this post helps click Yes
 
gootroots said:
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.
Click to expand...


Explain why "compact" is the desired result. The last match, the first match
after a blank, the first match alphabetically...
 
Hi Jacob Skaria
If you mean to return the last matching entry try the below.
Click to expand...

actually I do want to return the last matching entry but be able to return
any one of the entries at random.
 
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 Tools>AddIns>check 'Analysis tool Pak'> OK

If this post helps click Yes
 
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.
 
Glenn

Please see my latest post.

Glenn said:
Explain why "compact" is the desired result. The last match, the first match
after a blank, the first match alphabetically...
.
Click to expand...
 
Back
Top