4

I have this sheet.

Three Criteria

I want to lookup the value of Execution (%) based on three criteria: Year, Week, and Machine. So I want to tell the lookup to search for Year: 2018, Week: 2, Machine: Filler, I should get the number 100%. I want to do the same for CILT date, Duration (%), and Score.

VLOOKUP seems very limited for this sort of thing.

The information about INDEX and MATCH that I found is not well explained.

Can VLOOKUP do this? If not, what is the way to retrieve the desired values?

I have this formula

=INDEX('L5 Data'!M5:M15,SUMPRODUCT(--('L5 Data'!A5:A15="2018")('L5 Data'!B5:B15="2")('L5 Data'!F5:F15="Filler")*ROW('L5 Data'!5:15)))

But it returns the value one row down. In other words, for the Year: 2018, Week: 2, Machine: Filler, it returns 81.8% instead of 100%. What could be the reason?

2
  • You do have * between each of the )( , don't you? I would suggest subtracting 4 from the result of the SUMPRODUCT(). Currently, it will return the row number where your three conditions are true, i.e. 8. Since your index table starts in row 5, your formula should return the contents of M12, which is blank. I'm not sure why you're getting M9. Jan 15, 2018 at 2:56
  • I think you're looking for SUMIF() this would sum based on your criteria. read more: techonthenet.com/excel/formulas/sumif.php
    – iSR5
    Jan 15, 2018 at 11:53

3 Answers 3

3

You have asked three questions.

One is: "Can VLOOKUP() be used to do this?" Miroxlav has posted an excellent answer showing how to do this using a helper column.

Your second question asks how to get your desired result, and your third asks what could be the reason for your current formula returning the wrong answer. I'll try to answer the last two.

Your formula is very close to working correctly. Let's do some troubleshooting to find out what's wrong.

First, your formula has a couple of typos - the multiplication sign is missing in 2 places between parentheses. Excel suggested a correction for this, and I've removed the data sheet references ('L5 Data'!) for simplicity:

=INDEX(M5:M15,SUMPRODUCT(--(A5:A15="2018")*(B5:B15="2")*(F5:F15="Filler")*ROW(5:15)))

Excel has a built-in troubleshooting feature where you can highlight part of a formula in the formula bar and type F9 to see the value of the highlighted portion of the formula.

Click on the cell containing your formula and then highlight the expression (A5:A15="2018") in the formula bar and hit F9 (function key 9). You'll see an array containing all FALSE values. This says that no cell in A5:A15 = "2018". That's because you have 2018 in quotes. If you remove the quotes and try again, you'll get the array {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} which is what you expect - the first 5 cells in the range are = 2018.

The same problem exists in the second expression - remove the quote marks around the 2 in (B5:B15="2"). The third expression (F5:F15="Filler") does need the quotes, because "Filler" is text rather than a number.

Now highlight (A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler") and hit F9. You'll see the result is {0;0;0;1;0;0;0;0;0;0;0}. What has happened here? Multiplying arrays of logical values converts True/False values into 1's and 0's and also performs the equivalent of the logical AND(). So this result says that there is only one position in the range of rows 5:15 where column A is 2018, column B is 2 and column F is "Filler". And that position is the fourth one.

Next your formula multiplies this array times the array of row numbers 5:15. Using F9, you can see that result - {0;0;0;8;0;0;0;0;0;0;0}. Now SUMPRODUCT() adds up the elements of that array and returns a result of 8, which is the row number where all three conditions are true. Again, you can see this result using F9.

And now here's the main problem - after these intermediate calculations, your formula is: =INDEX(M5:M15,8), which returns the 8th element from the range M5:M15. But you don't want the 8th element, you want the 4th one, because the range M5:M15 starts in row 5, and 100% is in the 4th cell in the range M5:M15.

So the solution is to subtract 4 from the SUMPRODUCT():

=INDEX(M5:M15,SUMPRODUCT((A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler")*ROW(5:15))-4)

This returns the correct result - 100%.

Notes:

  1. I couldn't get your formula to return 81.8%, no matter how hard I tried.
  2. The double unary negative (--) in your formula isn't necessary. It is often used to convert logical arrays into 1's and 0', but the multiplication of the arrays accomplishes that.

I hope this helps. The changes I've outlined will allow your formula to work correctly. Use Miroxlav's answer if you want to use VLOOKUP(). Best of luck.

1
  • Thanks a lot. This worked with the curly braces.{=INDEX('L5 Data'!M5:M15,SUMPRODUCT(('L5 Data'!A5:A15=$B$1)*('L5 Data'!B5:B15=2)*('L5 Data'!F5:F15=B12)*ROW('L5 Data'!5:15))-4)}
    – user823527
    Jan 16, 2018 at 3:31
1

Yes, VLOOKUP() can do this, bringing you the single value you need (If you need a sum, use SUMIF()).
In these cases I typically create and use compound key for VLOOKUP().
In your case, such a key simply contains values from all three columns.
It enables you to specify lookup criteria using three columns.

Details:

  1. Insert column A with heading Year|Month|Machine and formula (for A5)

    =B5 & "|" & C5 & "|" & D5
    

    So for A5 it will evaluate to 2018|1|Filler.

  2. Then spread the formula down.

  3. Wherever you want to do a lookup, assemble the same key and perform VLOOKUP(), for example:

    =VLOOKUP("2018|2|Filler", A5:R9999, 8, FALSE)
    
    • Adjust searched expression to what you want, e.g. 2018|1|Labeler or assemble it from values in other cells.
    • Adjust data range A5:R9999 to actual one.
    • Adjust value 8 to match column Execution (%), counting from left column of the above range.

Once you get this working, you can also create the similar mechanism for the lookup based on the other three columns. Just add another column to the left of the same data. (Reason: VLOOKUP() expects lookup column as the leftmost.)

Note: Typically it is important to have a separator (in the above example I used |) to keep the keys unique. Otherwise, 2018,10,Filler and 2018,1,0Filler will create the same key what is obviously incorrect. For separator, use character not present in values.

UPDATE: This solution works. In comments below, there were mentioned some issues and these are now corrected or points made more clear. Thanks to Rajesh for proofreading.

9
  • I've Down Vote the Answer, Reason 1, the Criteria you have used doesn't matches the OP, which is actually "2018,2,Filler". Basic reason is, this Formula would have not return any record even if the criteria matches. Jan 15, 2018 at 10:32
  • YES, I've tested the Formula then Down Voted it. Your Formula using the Criteria 2018|1|Filler doesn't Return ANY CELL VALUE. And what I mean by RECORD is the MATCH VALUE. Jan 15, 2018 at 10:46
  • And Yes, Excel returns Records also which MATCH the Criteria. Jan 15, 2018 at 10:48
  • @RajeshS – the formula needs adjustment before use, because the sample in question contains some skipped columns... Number 8 needs to be adjusted. I put the note into the answer.
    – miroxlav
    Jan 15, 2018 at 10:48
  • 1
    @miroxlav, I know that site guidelines call for civility, and I commend you for your exceedingly polite response. Rajesh, you have probably set a record for the most downvoted answers in the shortest time in the history of this site. And there's a good reason for that. I would politely suggest that you work on trying to earn respect with good, accurate answers, and refrain from downvoting anyone. – Bandersnatch 2 hours ago Jan 16, 2018 at 1:49
-1

Your Formula Should like this,

{=INDEX(K$96:K$100,SUMPRODUCT(($D$96:$D$100="2018")*($E$96:$E$100=2)*($I$96:$I$100="Filler")*ROW($96:$100)))}

NB: only You need to put " * " sign between every Data range, and don't use " - - " after SUMPRODUCT.

Note, it's CSE Formula so finish with Ctrl+Shift+Enter.

Also, use your Data range in place of mine.

Hope this help you.

3
  • My SUGGESTION is simple, if YOU DOWN VOTE the ANSWER, put the REASON help me to REALIZE and improvise the ANSWER. Jan 15, 2018 at 10:33
  • Now, I've included { } at both ends to make it the CSE formula and it was my mistake. While Paste it here I just missed the brackets. I guess, It was the reason to DOWN VOTE it. Jan 15, 2018 at 11:19
  • 2
    There could be many reasons, here are a few: 1. You didn't answer the OP's question. 2. You posted almost exactly (only the data ranges are different) the same formula that OP is already using, which isn't giving him the right answer. 3. The formula you posted gives the wrong answer. 4. Because of the use of Sumproduct, it is not an array formula, so it doesn't need to be entered with CSE. Jan 15, 2018 at 21:28

You must log in to answer this question.

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