Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Screwy lookup function
Not sure if I am doing something wrong but seem to be getting some
screwy results using the lookup function. Couple examples: 1. With this array: Alpha 6 Beta 3 Gamma 6 Delta 3 =LOOKUP(3,C7:C10,B7:B10) ----- Beta (gives the first 3 value in the list) =LOOKUP(6,C7:C10,B7:B10) ----- Gamma (gives the second 6 value in the list) 2. With this array: Alpha 10 Beta 3 Gamma 6 Delta 3 =LOOKUP(10,C7:C10,B7:B10) ---- Delta (why didn't it give alpha?) 3. With this array Alpha 2 Beta 3 Gamma 6 Delta 3 =LOOKUP(10,C7:C10,B7:B10) ---- #NA (can't find a 2-value?) What's going on here?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Screwy lookup function
From Help:
Important The values in lookup_vector must be placed in ascending order: ....,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Matt" wrote in message ps.com... Not sure if I am doing something wrong but seem to be getting some screwy results using the lookup function. Couple examples: 1. With this array: Alpha 6 Beta 3 Gamma 6 Delta 3 =LOOKUP(3,C7:C10,B7:B10) ----- Beta (gives the first 3 value in the list) =LOOKUP(6,C7:C10,B7:B10) ----- Gamma (gives the second 6 value in the list) 2. With this array: Alpha 10 Beta 3 Gamma 6 Delta 3 =LOOKUP(10,C7:C10,B7:B10) ---- Delta (why didn't it give alpha?) 3. With this array Alpha 2 Beta 3 Gamma 6 Delta 3 =LOOKUP(10,C7:C10,B7:B10) ---- #NA (can't find a 2-value?) What's going on here?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Screwy lookup function
Hi Matt
Lookup only works with data that is sorted ascending, that is the reason for your inconsistent results. Try Vlookup instead, with the optional 4th parameter set to FALSE or 0 Note, however, that the value being looked up has to within the first column of the range. You would need to mark column BCutmove to column A.Insert cut cells Then =VLOOKUP(10,A1:B4,2,0) If you didn't want to rearrange your columns, then use Index / Match =INDEX(A1:A4,MATCH(10,B1:B4,0)) -- Regards Roger Govier "Matt" wrote in message ps.com... Not sure if I am doing something wrong but seem to be getting some screwy results using the lookup function. Couple examples: 1. With this array: Alpha 6 Beta 3 Gamma 6 Delta 3 =LOOKUP(3,C7:C10,B7:B10) ----- Beta (gives the first 3 value in the list) =LOOKUP(6,C7:C10,B7:B10) ----- Gamma (gives the second 6 value in the list) 2. With this array: Alpha 10 Beta 3 Gamma 6 Delta 3 =LOOKUP(10,C7:C10,B7:B10) ---- Delta (why didn't it give alpha?) 3. With this array Alpha 2 Beta 3 Gamma 6 Delta 3 =LOOKUP(10,C7:C10,B7:B10) ---- #NA (can't find a 2-value?) What's going on here?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help w/ LOOKUP function!! | Excel Worksheet Functions | |||
how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
how do i use LOOKUP and AND function | Excel Worksheet Functions | |||
Lookup Function | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |