Lookup nearest value (Index & Match)

K

karthik_mails

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
 
D

Domenic

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

return multiple records matching multiple criteria 2
Return multiple records matching multiple criteria 7
INDEX MATCH AND ?? 2
With F1 value, look at A, if match or partial match return B 5
Index and Match 8
Returning row and column headers of a value 3
Convert Number to Text for Index/Match 1
Index Match on Columns and Rows 0

Top