Index & Match Question

[Iriemon]

One last question:

How would this be modified to find the closest or next smaller value?

=INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'!$BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0))


For example, if I am trying to match .625 and 5000 and the table has:


..625 4000 .55
..625 4750 .65
..625 5500 .25
..625 6000 .35

How do I get it to return the .65?

 

[Mike H]

Hi,

Maybe this

=OFFSET(INDEX(C1:C4,MATCH(1,(A1:A4=D1)*(B1:B4>=E1),0)),-1,0)

With 0.625 in D1
and 5000 in E1

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[T. Valko]

What should happen if the 2nd lookup value is less than the samllest value
or greater than the highest value?

For example, lookup 0.625 and 3550, or, lookup 0.625 and 7000.