Index and Match where one of the lookups needs to be nearest to

[Alkaline198]

I have a index and match function trying to lookup a MF in a table using multiple match criteria. The problem is that one of the criteria to look up- the rate - is not going to match the table so i need to have it find the nearest one. Can someone look at the attached sheet and see if they can figure this one out???

=INDEX(G12:M25,MATCH(1,(L12:L25=C11)*(M12:M25=C7)*(H12:H25=C9),0),3)

Using this formula in C18 as the lookup. C9 needs to find the nearest in Column H

help!

[boopathiraja]

Try

Please Login or Register  to view this content.

[ConneXionLost]

If you sort your table (ascending) by "rate", then you could try this array formula:

Please Login or Register  to view this content.

Confirm as an array formula with Ctrl+Alt+Enter.

Cheers,

[Alkaline198]

Doesnt work unless Rate is exactly as in the table, similar to mine


Report::: Wait, Conne has it!! THanks!

[Alkaline198]

How can I expand this to also lookup the GTY to the nearest?

[ConneXionLost]

Do you mean the rate to the nearest and the GTY to the nearest? Or just the GTY to the nearest?

If just the GTY, then build another lookup table and sort it by GTY.

[Alkaline198]

Do you mean the rate to the nearest and the GTY to the nearest? Or just the GTY to the nearest?

If just the GTY, then build another lookup table and sort it by GTY.

Rate and GTY. Eventually there will be many many lookups within 10's of thousands of rows of data. I am trying to wrap my head around this type of match

[cbatrody]

Hi,

Please try the following formula in C15:

=SUMPRODUCT(--(L9:L22=C8),--(M9:M22=C4),--(H9:H22=INDEX(H9:H22,MATCH(C6,H10:H22,1)+1,1)),I9:I22)

[Alkaline198]

Also... is there a way to make it look for the closest value and not just -1,1 (next greater, next less)