Is INDEX,MATCH the best way?

[M.A.Tyler]

here is the problem:

A B C
1 Apple .40 =Index(A1:A5,Match(Large(B1:B5,1),0))
(Returns-Peach)
2 Orange .30 =Index(A1:A5,Match(Large(B1:B5,2),0))
(Returns-Apple)
3 Banana .20 =Index(A1:A5,Match(large(B1:B5,3),0))
(Returns-Apple)
4 Pear .40 =Index(A1:A5,Match(Large(B1:B5,4),0))
(Returns-Orange)
5 Peach .50 =Index(A1:A5,Match(Large(B1:B5,5),0))
(Returns-Banana)

Is there a way to have the formula in C3 return Pear?

 

[T. Valko]

Try this array formula** :

Item = column A
Cost = column B

=INDEX(Item,MATCH(LARGE(Cost-ROW(Cost)/10^10,ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0))

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[M.A.Tyler]

OK, now I'm getting a VALUE error. Could it be possible that if some of the
cells in column A (Items) are blank or "" that the division involved would
cause this error? More importantly is there a fix?

 

[T. Valko]

Could it be possible that if some of the cells in column A

(Items) are blank or "" that the division involved
would cause this error?

Blanks in column A aren't a problem but if you have formula blanks in column
B you'll get #VALUE! errors.

Try this version that accounts for formula blanks in column B (still an
array formula):

=INDEX(Item,MATCH(LARGE(IF(ISNUMBER(Cost),Cost-ROW(Cost)/10^10),ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0))

 

[M.A.Tyler]

I think closer, but now a #NUM! error for the blank cells?

 

[T. Valko]

What version of Excel are you using?

 

[M.A.Tyler]

2003

What version of Excel are you using?

 

[T. Valko]

Try this (array entered):

=IF(ROWS(C$1:C1)<=COUNT(Cost),INDEX(Item,MATCH(LARGE(IF(ISNUMBER(Cost),Cost-ROW(Cost)/10^10),ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0)),"")

Here's another less complicated approach that uses a helper column.

Items in the range A1:A10
Cost in the range B1:B10 (may contain formula blanks)

Enter this formula in C1 and copy down to C10:

=IF(COUNT(B1),B1-ROW()/10^10,"")

Enter this formula in D1 and copy down to D10:

=IF(ROWS(D$11)<=COUNT(C:C),INDEX(A:A,MATCH(LARGE(C:C,ROWS(D$11)),C:C,0)),"")

You can hide the helper column if desired.

 

[M.A.Tyler]

That's it, working great! Normally I understand them afterwards, but I'm lost
on this one. for instance what does 10^10 mean?

Also is there any benifit to using your second option, as opposed to the
Array version?

 

[T. Valko]

what does 10^10 mean?

It means 10 to the 10th power or:

10*10*10*10*10*10*10*10*10*10

=10,000,000,000

In order to do this task we need to make each Cost a unique number. We do
that in this expression:

Cost-ROW(Cost)/10^10

Or

B1-ROW()/10^10

Consider this example:

B1 = 40
B2 = 40

Here's how we make those 2 values unique:

B1 = 40: =40-(1/10,000,000,000) = 40-0.0000000001 = 39.9999999999
B2 = 40: =40-(2/10,000,000,000) = 40-0.0000000002 = 39.9999999998

Now we have unique numbers for Cost and can extract the Item that
corresponds to to each unique Cost.

Also is there any benifit to using your second option, as opposed to the
Array version?

Array formulas *usually* take longer to calculate, are *usually* more
complex and a lot of users don't know or forget about array entering (CTRL,
SHIFT, ENTER).

The other method requires the use of 2 formulas to accomplish what the array
formula can do by itself. If your primary concern is efficiency due to very
large numbers of calculation intensive formulas then you'd probably want to
use the non-array method.

Personally, I hate having to use helper formulas but I know that when the
situation demands max efficiency the helpers are the way to go. There are
also rare occasions where something is so complex that it can't be done in a
single formula (at least, I can't do it in a single formula).

 

[M.A.Tyler]

Thanks for taking the time to help with my formula problem, and the
explanation!

 

[T. Valko]

You're welcome. Thanks for the feedback!