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

  skeletal 12:44 25 Aug 2010

Can you use HLOOKUP?

You would also need to swap your rows around. I took your example and moved Tom/Perry etc to the third row; I kept your numbers in the second row.

I then used:

=HLOOKUP(MIN(A2:D2),A2:D3,2,FALSE)

in cell F3 and it retuned the name associated with the lowest number irrespective of whether row 2 was sorted or not.

Skeletal

  VoG II 12:52 25 Aug 2010

Try

=INDEX(1:1,MATCH(MIN(2:2),2:2,0))

  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.

  VoG II 20:44 25 Aug 2010

No problem but you'll have an issue if there are ties. Both Index/Match formulas will return the leftmost match only.

  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.

  VoG II 21:33 25 Aug 2010

If that's easy I would like to know the solution!

  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

Galaxy Note 8 vs iPhone X

The secrets of creating gory VFX

How to update iOS on iPhone or iPad

WhatsApp : comment lire vos messages sans que l’expéditeur le sache