Need value looked up based on SMALL() result

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jorabi - 03 Aug 2007 00:30 GMT

I have a sheet like this:

cat dog horse rat bird
9 6 8 1 3

I use the SMALL function to get the smallest three numbers

G2=SMALL(A2:E2,1)=1
H2=SMALL(A2:E2,2)=3
I2=SMALL(A2:E2,3)=6

I need to find a function that will return the corresponding
results

rat
bird
dog

Reply to this Message

Pete_UK - 03 Aug 2007 01:03 GMT

Put this formula in G1:

=INDEX($A$1:$E$1,MATCH(G2,$A$2:$E$2,0))

and copy across to H1 and I1.

Hope this helps.

Pete

> I have a sheet like this:
>
[quoted text clipped - 13 lines]
> bird
> dog

Reply to this Message

Jorabi - 03 Aug 2007 03:21 GMT

Perfect! Thanks!

> Put this formula in G1:
>
[quoted text clipped - 23 lines]
>> bird
>> dog

Reply to this Message

Jorabi - 03 Aug 2007 03:29 GMT

Oops, I spoke too soon. I should have mentioned that sometimes the numerical
values are the same. But I need to make sure the corresponding label comes from
the actual column, not just any matching value. Another example:

cat dog horse rat bird
9 2 8 2 3

I use the SMALL function to get the smallest three numbers

G2=SMALL(A2:E2,1)=2
H2=SMALL(A2:E2,2)=2
I2=SMALL(A2:E2,3)=3

I need to find a function that will return the corresponding
results

G1=dog
H1=rat
I1=bird

Your suggestion will return dog, dog, bird. Can it be adjusted?

> Put this formula in G1:
>
[quoted text clipped - 23 lines]
>> bird
>> dog

Reply to this Message

PapaDos - 03 Aug 2007 07:38 GMT

In G1:
=INDEX($A$1:$E$1, MOD( SUMPRODUCT( SMALL( $A$2:$E$2 + COLUMN( $A$2:$E$2 ) /
1024, COLUMN(A:A) ) ), 1 ) * 1024 )

In G2:
=SMALL( $A$2:$E$2, COLUMN(A:A) )

Drag/Fill to the right as needed...

Signature

Regards,
Luc.

"Festina Lente"

> Oops, I spoke too soon. I should have mentioned that sometimes the numerical
> values are the same. But I need to make sure the corresponding label comes from
[quoted text clipped - 45 lines]
> >> bird
> >> dog

Reply to this Message

Jorabi - 06 Aug 2007 13:29 GMT

Thank you. For some reason I can get your formulas to work fine on
my sample tiny worksheet, but I cannot get it to work on my real wks,
which is considerably more complex. I haven't been able to locate
the difference. Maybe it would help if I understood what the 1024 is
for?

If you are agreeable, I will post the relevant part of my worksheet for
you to look at. Thanks for your help.

> In G1:
> =INDEX($A$1:$E$1, MOD( SUMPRODUCT( SMALL( $A$2:$E$2 + COLUMN( $A$2:$E$2 ) /
[quoted text clipped - 55 lines]
>> >> bird
>> >> dog

Reply to this Message

PapaDos - 08 Aug 2007 22:38 GMT

The 1024 is just a divisor I used to encode the column number as the
fractional part into your data. Post a more realistic example of your data...

Signature

Regards,
Luc.

"Festina Lente"

> Thank you. For some reason I can get your formulas to work fine on
> my sample tiny worksheet, but I cannot get it to work on my real wks,
[quoted text clipped - 64 lines]
> >> >> bird
> >> >> dog

Reply to this Message

Rate this thread: