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:
- I couldn't get your formula to return 81.8%, no matter how hard I tried.
- 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.