Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match Index Lookup
Hi - have done this in the past for unfortunately forget
I have a list of days in number format say from 365 in my table. I have set up a list of then 12 mths so in excel I have in cell A1 = 365, B1= 12mths A2 = 335, B2 = 11mths etc I want to lookup a value in my table and match it to my list so that for example if the value is 360, the lookup would go though my list and realise that 360 is less than 365 and skip to next value and then look to cell A2 as as 360 is above the value in A1 it returns the value in B2. I think it is quite a simple match / index type lookup (where you have to have you values in a list with the highest value first in the list) All advice much appreciated thanks -- Kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match Index Lookup
You must sort your lookup array in ascending order on column A1
Assuming the value you lookup is in C1 and your lookup array in A1:B99, type: =VLOOKUP(C1,A1:B99,TRUE) HTH -- AP "Kevin" a écrit dans le message de ... Hi - have done this in the past for unfortunately forget I have a list of days in number format say from 365 in my table. I have set up a list of then 12 mths so in excel I have in cell A1 = 365, B1= 12mths A2 = 335, B2 = 11mths etc I want to lookup a value in my table and match it to my list so that for example if the value is 360, the lookup would go though my list and realise that 360 is less than 365 and skip to next value and then look to cell A2 as as 360 is above the value in A1 it returns the value in B2. I think it is quite a simple match / index type lookup (where you have to have you values in a list with the highest value first in the list) All advice much appreciated thanks -- Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup? Index? match? formula | Excel Discussion (Misc queries) | |||
Lookup nearest value (Index & Match) | Excel Worksheet Functions | |||
Lookup nearest value (Index & Match) | Excel Worksheet Functions | |||
Large Index Match Lookup | Excel Worksheet Functions | |||
lookup, index, match, offset, etc. | Excel Worksheet Functions |