协助使用INDEX和MATCH与两个标准 - 简单的查找表 [英] Assist with using INDEX and MATCH with Two Criteria - simple lookup table

问题描述

我试图找出一些很好的记录，但是我并没有完全了解它：尝试使用INDEX和MATCH匹配一个单元格，基于2个条件。

解决方案

对于单元格G2，尝试这个公式 = INDEX（M：M，MATCH（1，（J：J = F2）*（L：L = H2），0））。这遵循与您阅读的相同的基本思想，但有点简单。

如您所引用的文章所示，这是一个数组公式，所以它必须按Ctrl-Shift键。

I'm trying to figure out something that is nicely documented, but I'm not quite getting my head around it: trying to match a cell using INDEX and MATCH based on 2 criteria.

http://exceltactics.com/vlookup-multiple-criteria-using-index-match/

The example documented above is a more complex case than mine, and with more columns. My case is simpler, but the model still fits. First, here is my spreadsheet with lookup table embedded (green block):

The light-green block is my lookup table containing all sites and subsites. The ultimate objective is to grab the SubSiteID based on the Site and SubSite. Notice the red text in cells G2-G4. These are the desired outcomes for the top 3 rows. To discover the SubSiteID, first find the site, which is Farmington Bus Park. In the green block you'll see that twice, but only 1 row with "West" in the subsite - thus we find the correct SubSiteID of 569.

The 2nd example (Clearfield High School) has no SubSite, so the SubSiteID is 0. Easy.

The 3rd example (Clinton City Park) has a subsite of "West", so by looking in the green block you'll find the SubSiteID of 558.

I need a formula that will look at F2 and H2 (for example), find their matches in the green table, and return the SubSiteID when there is one.

You can ignore both SiteID columns, unless it makes more sense to base the lookup on that, rather than the site name.

The example formula in the article noted above is this:

=INDEX(C4:H1159,MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0),6)

I'm having trouble applying that formula to my particular case. If you can show the formula to match my worksheet I'll be most grateful.

Spreadsheet: http://www.usaarbiters.com/Schedule.xlsx

解决方案

For cell G2 try this formula =INDEX(M:M,MATCH(1,(J:J=F2)*(L:L=H2),0)). This follows the same basic idea as the one you read about, but a bit simpler.

As indicated in the article you cite, this is an array formula so it has to be Ctrl-Shift-Enter'ed.

这篇关于协助使用INDEX和MATCH与两个标准 - 简单的查找表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！