Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup help
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup help
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) "madmxx22277" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup help
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 "madmxx22277" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup help
A simple VBA procedu
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "madmxx22277" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel |