2

In using INDEX and MATCH to perform a multiple-condition VLOOKUP, I'm currently getting results for the desired column using the below formula, but the result is consistently 1 row lower than desired.

{=INDEX($A$2:$E$1200,MATCH(1,(A:A=A2)*(E:E="WS01"),0),3)}

In the attached image, we see Row 4's S/N returned for row 3's result in finding a "WS01" entry. If I replace the multiplier (*) in MATCH with an addition, I get the correct result for the first store (#2), but then for all following rows, the formula ends up providing row 4's S/N (REG01), so that doesn't seem to be the right path either.

enter image description here

2

The way those functions work is the following

Index takes an array (A2,A3,A4...) as well as a number

=Index(A2:A4,2) would return the value in A3 since it is the second cell in the array.

Match looks through an array and returns the number of the cell it would be found in (2nd cell, 4th cell, etc) it doesn't return the actual cell position.

So by not using matching arrays in the two functions

  • Index uses C2:C1000
  • Match uses A:A

You are causing your mismatch. Match function finds that in the 3rd line (A3) everything matches so it returns a 3.

Index then takes (C2:C1000,3) and it returns the 3rd cell which happens to be C4 not C3.

Note for the array function you can just use C2:C1000, not A2:E1000 then you can omit the column part of the function.

| improve this answer | |
  • I don't follow. My INDEX array is $A$2:$E$1200, not C2:C1000. Can you clarify, or provide an example? – David Metcalfe Oct 9 '15 at 21:14
  • 1
    @DavidMetcalfe your INDEX() array starts at row 2. Your MATCH() array starts at row 1. That is the problem. They both have to start at the same row. – Kyle Oct 9 '15 at 21:25
  • @Kyle Ah, I see what you mean and what gtwebb was likely trying to point out now. – David Metcalfe Oct 9 '15 at 21:34
  • Yes indeed, my note at the end was that you don't need to say A2:E1200 if you are only returning values from column C. – gtwebb Oct 9 '15 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.