# INDEX MATCH based on 2 Criteria [duplicate]

I'm struggling to create a compound INDEX MATCH in Excel based on two criteria.

My data set looks like this:

``````RANGE       SITE    TYPE

2711260     LOB     LBO

2711260     MYP     WAN

2711266     GCT     LBO

2711516     EEP     WAN

2711516     EFC     WAN

2711516     LOB     WAN

2711516     MWP     LBO
``````

I would like to use an INDEX/MATCH as follows:

- Given a RANGE, return SITE, on condition that TYPE ="LBO".

In my instance, a of TYPE "LBO" is unique for every RANGE. So for example, if my input data is 2711516, I'd like "MWP" returned, not "EEP", "EFC"or "LOB".

I'm not sure if this is a nested INDEX MATCH statement. Please can someone assist.

• I think I understand, but to clarify, is there a chance of duplicate matches? In other words, could there be a RANGE number with more than one instance of "LBO" as TYPE? Mar 12, 2014 at 15:09
• In my current scenario, no. Mar 12, 2014 at 15:11

If your range was in cell `D1` and your type was in cell `E1` this formula would return MWP

`=INDEX(A2:C8,MATCH(1,(A2:A8=D1)*(C2:C8=E1),0),2)`

This is an array formula, so you need to type it in and enter it with crtl+shift+enter.

Basically your match is saying look for "true" if these two conditions are both "true".

• Thanks, I understand what you're trying to do, but you colunm references are throwing me off here... If RANGE in A:A, SITE in B:B and TYPE in C:C, would this then be: `=INDEX(B:B,MATCH(1,(A:A=D1)*(C:C=E1),0),2)` Mar 12, 2014 at 14:59
• No, the references are correct. You can change the A:A and C:C but not the first array. The `2` specifies the 2nd column. Mar 12, 2014 at 15:11
• Awesome. I did some googling after your solution and found that the form `=INDEX(D:D,MATCH("LBO",IF(C:C=2711260,E:E),0))` also worked where RANGE in C:C, SITE in D:D and TYPE in E:E. Input values were hardcoded for the test. What is the difference between solutions, and would either be preferred (and why)? Mar 12, 2014 at 15:16
• You're looking for if C equals, then return E and match to LBO to return D. Mine is more elegant, but there's no real difference unless you're looking to change any part of it. Mar 12, 2014 at 15:21
• Is the solution column ordering dependent though? My actual data has 1 or 2 columns in between, and column ordering is different - getting NA error. May have to do some reading up on excel arrays, but this is great for now. Mar 12, 2014 at 15:36

Until someone offers a cleaner solution: you could create a helper column containing the concatenated values of range and type columns (using either CONCATENATE function or & operator) and in your MATCH function use input&"LBO" for searching.

Copy the formula to a new sheet into columns B,C,D

I have combined vlookup and if formular:

``````=vlookup(b5,\$b\$5:\$b\$17,IF(AND(B5=2711516,c5="MWP"),3,4)1)
``````

Note:

While the columns are only 3, I have added a fourth column in the table range.

You can now use iferror to remove the 0 values

For a cleaner solution, do the following:
1. Remove the blank spaces in between rows
2. Use the formula below assuming your first data is in cell B6

``````=IF(VLOOKUP(B6,\$B\$5:\$E\$23,IF(AND(B6=2711516,C6="MYP"),3,4),1)=0,"",VLOOKUP(B6,\$B\$5:\$E\$23,IF(AND(B6=2711516,C6="MYP"),3,4),1))
``````

NOTE:
An extra column is added to allow for a column number that takes care of other data