index match with multiple criteria in rows

Question

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

Scott Craner · Accepted Answer · 2018-01-26 21:59:08Z

2

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

edited Jan 26, 2018 at 21:59

answered Jan 26, 2018 at 21:51

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Manya Mohan Over a year ago

thank you for your quick reply. =INDEX(J2:J57,MATCH(1,(L4=B2:B57)*(M4>=I2:I57),0)) this gives me a #N/A error

Scott Craner Over a year ago

Then you did not use Ctrl-Shift-Enter instead of Enter when exiting edit mode like I stated int he answer. Be that as if may the formula was a little wrong, see edit for correct formula.

Manya Mohan Over a year ago

I had converted into array formula by pressing CSE. I had put > sign instead of < which is why it was showing an error. Anyway, thanks a lot for your help

BruceWayne Over a year ago

@ManyaMohan - if this worked for you, you can flag it as The Answer by clicking the check mark left of the post, below the up/down arrows.

Collectives™ on Stack Overflow

index match with multiple criteria in rows

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related