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

My data set looks like this:


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?
    – CharlieRB
    Mar 12, 2014 at 15:09
  • In my current scenario, no.
    – jonathan
    Mar 12, 2014 at 15:11

4 Answers 4


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


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)
    – jonathan
    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)?
    – jonathan
    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.
    – jonathan
    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:



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


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

Not the answer you're looking for? Browse other questions tagged .