1

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.

Improve this question
2
  • 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
    Commented Mar 12, 2014 at 15:09
  • In my current scenario, no.
    – jonathan
    Commented Mar 12, 2014 at 15:11

4 Answers 4

Reset to default
1

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".

Improve this answer
6
  • 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
    Commented 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.
    – Raystafarian
    Commented 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
    Commented 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.
    – Raystafarian
    Commented 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
    Commented Mar 12, 2014 at 15:36
1

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.

Improve this answer
-1

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

Improve this answer
-1

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

Improve this answer

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