Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup nearest value (Index & Match)
Hi
I have a sheet with the following data SE 150 Low SE 180 Med SE 200 Hi SSE 160 Low SSE 175 Med SSE 220 Hi I need a combination of Index and match formula to look up the first column for exact match i.e, Col 1 SE/SSE and column 2 for nearest match i.e, the number and return the 3rd column example if i have SE 175 the value i need is Low ex 2: i have SE 190 the value i need is med i have SSE 170 the value i need is low Thanks in advance Karthik |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup nearest value (Index & Match)
Assumptions:
A1:C6 contains your data The data is sorted by Column A, in ascending order E1 contains the first criteria, such as SSE F1 contains the second criteria, such as 175 Formula: =LOOKUP(F1,INDEX(B1:B6,MATCH(E1,A1:A6,0)):INDEX(C1 :C6,MATCH(E1,A1:A6))) Hope this helps! In article .com, wrote: Hi I have a sheet with the following data SE 150 Low SE 180 Med SE 200 Hi SSE 160 Low SSE 175 Med SSE 220 Hi I need a combination of Index and match formula to look up the first column for exact match i.e, Col 1 SE/SSE and column 2 for nearest match i.e, the number and return the 3rd column example if i have SE 175 the value i need is Low ex 2: i have SE 190 the value i need is med i have SSE 170 the value i need is low Thanks in advance Karthik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup nearest value (Index & Match) | Excel Worksheet Functions | |||
MATCH, INDEX, LOOKUP - Help! | Excel Worksheet Functions | |||
Large Index Match Lookup | Excel Worksheet Functions | |||
lookup, index, match, offset, etc. | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |