索引与行中的多个条件匹配 [英] index match with multiple criteria in rows

问题描述

我正在处理一个电子表格，其中在B列中有股票代号，在I列中有与那些代号相对应的成员，在J列中有比率.

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.

在我的输出中，我需要特定股票代码ID和成员的汇率.它应该查找确切的股票代号(如果没有).的成员不存在于数据中，则应选择小于或等于lookup_value的最大值.

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.

我尝试过

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

但这是在寻找完全匹配，但是我需要1个条件的完全匹配且大于第二个条件

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

推荐答案

使用:

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

这是一个数组公式，退出编辑模式时，需要使用Ctrl-Shift-Enter而不是Enter进行确认.如果正确完成，则Excel将在公式周围放置{}.如果操作不正确，您会收到#N/A

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

这篇关于索引与行中的多个条件匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！