Need value looked up based on SMALL() result

  • Thread starter Thread starter Jorabi
  • Start date Start date
J

Jorabi

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
 
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
 
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?
 
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...
 
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.
 
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...
 
Back
Top