Vlookup help

[Guest]

say I got a table like this
Bob 4
Charlie 7
George 4
Oliver 2
Henry 8

I am using the large command to get me the 3 lowest numbers, and using
vlookup to match the name up with it, so I should end up with
Oliver 2
Bob 4
George 4
but since the last ones are the same number I end up with,
Oliver 2
Bob 4
Bob 4

please help, is there a way I can have vlookup do its thing, but have it not
be equal to another vlookup cell?

 

[Ron Coderre]

With
C1: 1
C2: 2
C3: 3

and...using your posted data.....

These seem to work:

ARRAY FORMULA, committed with Ctrl+Shift+Enter (instead of just Enter):
D1:
=INDEX($A$1:$A$5,MATCH(SMALL($B$1:$B$5+ROW($B$1:$B$5)/100,C1),$B$1:$B$5+ROW($B$1:$B$5)/100,0))
Copy D1 into D2 through D3

or...this longer NON-array formula:
D1:
=INDEX($A$1:$A$5,MATCH(SMALL(INDEX($B$1:$B$5+ROW($B$1:$B$5)/100,0),C1),INDEX($B$1:$B$5+ROW($B$1:$B$5)/100,0),0))

Note: In case text wrap inpacts the display, there are NO spaces in those
formulas.

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

You can try a variant on the ranking function

=RANK(B2,$B$2:$B$12)+COUNTIF($B$2:$B2,B2)-1
will add one to the 2nd onwards of each duplicated score.

Peter

 

[Bernard Liengme]

A simple VBA procedure:
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 19/10/2007 by Bernard V Liengme
'

'
Range("A1:b1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D4:E4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End Sub