#1
|
|||
|
|||
something like VLOOKUP
I'm checking a value against a table, I need behavior like a VLOOKUP table except I wan't to take the value less than the table value.
For example, I have a value, say 230. I want to look up which of these its closest to, but less than: 1 110 2 220 3 340 4 460 5 503 6 620 7 700 8 890 9 910 10 1120 So I wan't to return "3", as its less than 340. I can't use VBA. Does anyone know how I can do this? Cheers |
#2
|
||||
|
||||
Hi,
=INDEX(A1:A10,MATCH(D1,B1:B10)+(LOOKUP(D1,B1:B10)< D1)) where A1:B10 contains the table you posted and D1 contains the value to look up (eg 340). |
#3
|
|||
|
|||
Thank you, that works really well!
|
#4
|
|||
|
|||
Off topic: Congratulations Colin, just noticed you are a Super Moderator now... I was wondering how is Paul dealing alone with all the work
|
#5
|
||||
|
||||
Thanks Catalin!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using Vlookup & IF together | thelauncher | Excel | 5 | 08-25-2013 11:32 PM |
Vlookup | ibrahimaa | Excel | 5 | 03-04-2012 11:24 AM |
Vlookup | ibrahimaa | Excel | 8 | 01-03-2012 09:32 PM |
Vlookup | Karen615 | Excel | 4 | 09-12-2011 02:30 PM |
Help with VLOOKUP | sakhtar | Excel | 2 | 07-24-2010 07:39 PM |