index match with multiple criteria in rows

0
votes

I am working on a spreadsheet in which I have ticker id in column B, members corresponding to those tickers in column I and rates in column J.

Rates change depending upon the no. of members, so one ticker ids can be in multiple rows.

In my output, I need the rate for a particular ticker id and member. It should lookup exact ticker id but if no. of members is not there in data then it should pick the largest value that is less than or equal to lookup_value.

I have attached snapshot of the data and desired output. any help will be highly appreciated.

I have tried 

=INDEX(J2:J57,MATCH(1,(L2=B2:B57)*(L4=I2:I57),0))

but this is looking for exact match however I need exact match for 1 criteria and greater than match for second

enter image description here

1
excelindexingmatchformula

1 Answers

2
votes

Use:

=INDEX($J$2:$J$57,MATCH(1,(L3=$B$2:$B$57)*(M3<=$I$2:$I$57),0))

This is an array formula and needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula. If not done correctly you will receive #N/A

enter image description here