Excel LOOKUP on unsorted data

  powerless 19:14 24 Aug 2010
Locked

A1: Tom B1: Perry C1: Merry D1: John
A2: 1 B2: 2 C2: 3 D2: 4

click here if the above displays all wonky.

I want to find the minimum value in row 2 and its name in row 1.

I'm using =LOOKUP(MIN(A2:D2),A2:D2,A1:D1) which returns Tom.

But if the values in row 2 are unsorted the LOOKUP returns #N/A.

How do you find the name in row 1 when the values in row 2 are unsorted?

I've looked at this click here but I cannot figure it out.

Any ideas?

  johnnyrocker 20:38 24 Aug 2010

i shall bump it for you and maybe someone will offer help maybe vog?



johnny

  Picklefactory 13:03 25 Aug 2010

... my suggestion was going to be
=INDEX(A1:A4,MATCH(MIN(B1:B4),B1:B4,0))
But VoG™ is the man.
:o)

  powerless 20:29 25 Aug 2010

HLOOKUP would not work as the data needs to be the way it's seen as above.

But =INDEX(1:1,MATCH(MIN(2:2),2:2,0)) / =INDEX(A1:A4,MATCH(MIN(B1:B4),B1:B4,0)) worked.

Cheers VoG™ (long time no type) and Picklefactory.

  powerless 21:13 25 Aug 2010

You mean if there are two min values the same? If so it's OK, I'll add in something that shows that there are two value the same, easy enough.

  powerless 22:33 25 Aug 2010

Nothing fancy I added in more columns and instead of using MIN, I used SMALL. Then just compare the names and show if they match.

=INDEX(A1:D1,MATCH((E2),A2:D2,0)) - E2 cell ref instead on MIN.

In E2 {=SMALL(IF(A2:D2=0,"",A2:D2),1)} - First min val and ignore zeros. Repeat for second min val.

The only problem is if A1:D1 is all blank I get errors. click here not the end of the world - would start getting to involved than it really needs to be.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Oppo A9 2020 Review

The year's best animated short Hair Love is finally online

Mac Pro 2019 release date, price, features & specs

Cdiscount : la PS4 Pro 1 To + 5 jeux à moins de 400 €