Hot Topics

# 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

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

Xiaomi Mi 10 Launches Globally: Price, Release Date, Specs and more

Best Free, Deep Discounted tools for artists and designers 2020

How to get Apple's Final Cut Pro and Logic Pro free

Qwant moteur de recherche par défaut sur les derniers Huawei