Excel INDEX/MATCH not matching 3rd criteria

1
votes

I have 5 columns of data and I'm using an INDEX/MATCH function to pull data from column 5 based on exact criteria matches from columns 1, 3 & 4. So basically the user chooses 3 different criteria (from dropdowns) and if all three match columns 1, 3 & 4 exactly then the result(s) comes from column 5. Here's the function I'm using... 

{=IFERROR(INDEX(F5:F500,MATCH(1,(E5:E500=Production!B4)*(G5:G500=Production!B8)*(H5:H500=Production!B6),0)),"")}

Issue I had was depending on the criteria chosen there could be anywhere from 4 to 10 results so I used the following to pull the next results... 

{=IFERROR(INDEX(F5:F500,MATCH(1,(E5:E500=Production!B4)*(G5:G500=Production!B8)*(H5:H500=Production!B6),0)+1),"")}

For each line I just added another number on the end of the function (ie. "+2" "+3", and so on) to get the desired results. As I mentioned above some criteria will have less results and this is where my problem is... once I have all available results (for example 3 results) the next line ("+3") should spit out an error because there are no more results that match the criteria exactly but instead the function just gives me the next result that matches the first two criteria but not the third. I apologize if this seems confusing, a little hard to explain which is probably why I couldn't anything in the forums that helped.

Any advice would be very much appreciated. (Note: I removed the "$" from the above functions so they are easier to read) Thanks!

1
excelfunctionindexingmatch
Not to detract from the way you're doing it, but have you tried this formula "layout" for a multi-variable Index/Match? (Thanks for that last note, I agree that it helps legibility for us, but also lets us know you're aware of anchoring, so we don't have to get into that) – BruceWayne
@BruceWayne that will not fix this exact problem as he gets the correct answer. The OP wants to iterate and show all that apply. – Scott Craner
Into which cell does the first formula go? There is a method using countif() instead of arbitrarily adding row numbers. Your problem stems from the fact that you are not testing the values three rows dowm but finding the first then telling the INDEX to return the value three rows down. Excel does not know to exclude that if the values on that row do not match. – Scott Craner
@BruceWayne Thanks for the suggestion, I have tried that formula layout (plus a few others) and as Scott Craner mentioned I get the same results. – Dick Plixen
@ScottCraner the first formula can go in any cell really, it's on a data page so formatting isn't really an issue. I'm having trouble understanding what you mean with regards to the "three rows down" comment, I thought that by me adding the "+1", "+2", etc. it would just search out the next result that met that criteria but I guess it makes more sense that it's just going to the next line down considering they are all in order. Is there something I could add to the formula that searches the 2nd result that matches the criteria, 3rd result and so on? – Dick Plixen

1 Answers

2
votes

So in your example formula you would put this in M2:

=IFERROR(INDEX($F$5:$F$500,MATCH(1,($E$5:$E$500=Production!B4)*($G$5:$G$500=Production!B8)*($H$5:$H$500=Production!B6)*(COUNTIF($M$1:$M1,$F$5:$F$500)=0),0)),"")

Hit Ctrl-Shift-Enter as it is an array formula. Then copy down.

Note as I did not have test data this is untested.

I put the $ back in as the relative/absolute of my addition is very important.