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?

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

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


--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

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

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
I think closer, but now a #NUM! error for the blank cells?

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

2003

"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
I think closer, but now a #NUM! error for the blank cells?

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

Try this (array entered):

=IF(ROWS(C$1:C1)<=COUNT(Cost),INDEX(Item,MATCH(LAR GE(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$1:D1)<=COUNT(C:C),INDEX(A:A,MATCH(LARGE (C:C,ROWS(D$1:D1)),C:C,0)),"")

You can hide the helper column if desired.

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
2003

"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
I think closer, but now a #NUM! error for the blank cells?

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

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" wrote:

Try this (array entered):

=IF(ROWS(C$1:C1)<=COUNT(Cost),INDEX(Item,MATCH(LAR GE(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$1:D1)<=COUNT(C:C),INDEX(A:A,MATCH(LARGE (C:C,ROWS(D$1:D1)),C:C,0)),"")

You can hide the helper column if desired.

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
2003

"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
I think closer, but now a #NUM! error for the blank cells?

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

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



--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

Try this (array entered):

=IF(ROWS(C$1:C1)<=COUNT(Cost),INDEX(Item,MATCH(LAR GE(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$1:D1)<=COUNT(C:C),INDEX(A:A,MATCH(LARGE (C:C,ROWS(D$1:D1)),C:C,0)),"")

You can hide the helper column if desired.

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
2003

"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
I think closer, but now a #NUM! error for the blank cells?

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

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

"T. Valko" wrote:

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



--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

Try this (array entered):

=IF(ROWS(C$1:C1)<=COUNT(Cost),INDEX(Item,MATCH(LAR GE(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$1:D1)<=COUNT(C:C),INDEX(A:A,MATCH(LARGE (C:C,ROWS(D$1:D1)),C:C,0)),"")

You can hide the helper column if desired.

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
2003

"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
I think closer, but now a #NUM! error for the blank cells?

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
Thanks for taking the time to help with my formula problem, and the
explanation!

"T. Valko" wrote:

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



--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

Try this (array entered):

=IF(ROWS(C$1:C1)<=COUNT(Cost),INDEX(Item,MATCH(LAR GE(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$1:D1)<=COUNT(C:C),INDEX(A:A,MATCH(LARGE (C:C,ROWS(D$1:D1)),C:C,0)),"")

You can hide the helper column if desired.

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
2003

"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
I think closer, but now a #NUM! error for the blank cells?

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
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?