Confused with VLOOKUP

[Georgia Golfer]

I have grades in column A, the ranking from lowest to highest in column B & the names associated in column C.

In Column D, I want the names with the lowest grades to the highest in order. Can't figure out how to get it to work.

A B C D
60 2 Brad Conny
70 4 Jim Brad
65 3 Dave Dave
80 5 Alexis Jim
59 1 Conny Alexis
90 6 Benjamin Benjamin

[NBVC]

One way:

=VLOOKUP(ROWS($A$1:$A1),$B$1:$C$6,2,0)

copied down

[Georgia Golfer]

Thanks, what if 2 grades are the same, I'm getting #NA values.

[NBVC]

It would've been nice if the original sample showed that.. as it is a much more complicated process dealing with duplicates...

1st you need a helper column that lists the numbers (incl. duplicates) from smallest to largest..

formula in D1:

Please Login or Register  to view this content.

copied down.

then you need a formula to use those to capture the matches without repeating or giving #N/A! error.

formula in E1:

Please Login or Register  to view this content.

This formula must be confirmed with CTRL+SHIFT+ENTER and copied down.

[Georgia Golfer]

Hey NB, Sorry, I should have thought of that.

Thanks for the help, but the spreadsheet is still not recognizing and differentiating ties. If there are 2 59's both show Conny, then I get a #NUM error further down the page.

I tried to attach a copy of the spreadsheet.

[DonkeyOte]

Personally I'd adopt the following:

Change C formula to:

C2: =RANK($B2,$B$2:$B$101,1)+(ROW()/10000000)
Copy to C3:C101

Change F formula to:
F2: =INDEX($D$2:$D$101,MATCH(SMALL($C$2:$C$101,ROWS($E$2:$E2)),$C$2:$C$101,0),1)
Copy to F3:F101

Does that work for you ?

If you have 2 instances of the same number in B the first name listed in D will be that listed first in F etc...
(EDIT: it's worth noting that you could simplify further by making C = score + (row()/1000000) ... and get rid of need to use SMALL(...) in the F formula ... you could return C value in E (as opposed to B) given you're values in B are whole numbers the increment of the ROW / 10 million is irrelevant -- then just match the value in E to C in the INDEX of D)

[Gearcutter]

Hi,
I changed the formula that you had in col F to :

=INDEX($D$2:$D$101,SMALL(IF($B$2:$B$101=$E2,ROW($B$2:$B$101)-ROW($B$2)+1),COUNTIF($E$2:$E2,$E2)))
and it works ok
enter with control+shift+enter

[DonkeyOte]

However if you can I'd still advocate you avoid use of Array -- achieved by making the RANK value unique.

[Georgia Golfer]

It works, stupid question....

If someone doesn't take a test, so won't have a score in that area, I get a #VALUE. I tried changing the rank formula to an if statement, =IFB2>0, RANK.... but it doesn't like that. I'm still not very good at nesting mulitple functions, sorry.

[NBVC]

I really don't want to confuse the situation.. so since you have adopted another solution other than the one I offered, then I will let the other responder finish it off.

[DonkeyOte]

Isn't clear to me which method had been adopted (ie mine or the CSE approach as altered per Gearcutter's post) -- both approaches would run into a similar issue should there be blanks in B.

If using my approach with a revised rank and no-arrays, change the following:

C2:=IF(ISNUMBER($B2),RANK($B2,$B$2:$B$101,1)+(ROW()/10000000),1000+(ROW()/10000000))

If not using the revised rank (ie using the array approach in F) then altering the RANK such that:

C2:=IF(ISNUMBER($B2),RANK($B2,$B$2:$B$101,1),"")
should get rid of any errors you were seeing in Col C.

For either/or approach you should change the following:

E2: =IF(ROWS($E$2:$E2)>COUNT($B$2:$B$101),"",SMALL($B$2:$B$101,ROWS($E$2:$E2)))

F2:
encase existing formula within an IF test based on E2, eg:
=IF(ISNUMBER($E2),existing formula,"")

So for mine:

F2: =IF(ISNUMBER($E2),INDEX($D$2:$D$101,MATCH(SMALL($C$2:$C$101,ROWS($E$2:$E2)),$C$2:$C$101,0),1),"")

For NBVC's Array:
F2: =IF(ISNUMBER($E2),INDEX($D$2:$D$101,SMALL(IF($B$2:$B$101=$E2,ROW($B$2:$B$101)-ROW($B$2)+1),COUNTIF($E$2:$E2,$E2))),"")
Remember that if you're using the above you must reset the array with CTRL + SHIFT + ENTER -- once set will appear encased within {}

I hope that resolves your issue regardless of method employed.

[NBVC]

Hey NB, Sorry, I should have thought of that.

Thanks for the help, but the spreadsheet is still not recognizing and differentiating ties. If there are 2 59's both show Conny, then I get a #NUM error further down the page.

I tried to attach a copy of the spreadsheet.

As Gearcutter said, the formula in F2 should be:

Please Login or Register  to view this content.

CSE confirmed and copied down.

[Georgia Golfer]

Thanks everybody!