CODEWITHSUNDEEP
excelexcel-formulavlookup
HerrimanCoder
HerrimanCoder

Reputation: 7208

Assist with using INDEX and MATCH with Two Criteria - simple lookup table

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):

enter image description here

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

Upvotes: 1

Views: 463

Answers (2)

MattClarke
MattClarke

Reputation: 1647

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.

Upvotes: 0

HerrimanCoder
HerrimanCoder

Reputation: 7208

This turned out to be the correct answer:

=INDEX($M$2:$M$39,MATCH(F2,IF($L$2:$L$39=I2,$K$2:$K$39),0))

Upvotes: 1

Related Questions