Need formula to return exact match

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

 

[Jacob Skaria]

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

 

[Glenn]

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

 

[Gotroots]

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]

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

 

[Gotroots]

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.

 

[Gotroots]

Glenn

Please see my latest post.

Explain why "compact" is the desired result. The last match, the first match
after a blank, the first match alphabetically...
.

 

[Glenn]

Array formula:

=IF(SUM(--(uses!A1:A1000&uses!C1:C1000=B189&"compact")),"compact","")

 

[Gotroots]

Glenn

You have nailed it

Job well done!

From one very grateful excel user.