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

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