Index and Match Returning answer from above Row

0
votes

I have used Index and Match before and have checked the usual errors but I have never come across this! This is my formula - below - and when I do Ctrl Shft Ent, I get the result of 

£13.89!

The field B23 represents a company code and D23 represents an item code. The error is that the company AF13 has never purchased item TTX123 but it is returning a cost. This cost is the same as above. If I change the item code to "X" it replies with the answer but from the row below! The match data of e2:e3287 and g2:g3287 are in the correct order. This is driving me mad! Also, I have tried different layouts of this formula with the same answer. Please help!

=IFERROR(INDEX(LPP!$J$2:$J$3287, MATCH(DATA!$B23 & DATA!$D23, LPP!$E$2:$E$3287 & LPP!$G$2:$G$3287),0), 0)
1
excel
simplifying your model and providing some basic data would go a long way to getting help. – Forward Ed
Please enter it as an Array Formula as answered by @Matthias Tidlund . Your formula works correctly for me too. – skkakkar

1 Answers

1
votes

Since you concatenate two column values and then compare with two column values that also are concatenated you need to enter it as an array formula. Hold Shift+CTRL when pressing Enter to apply the function, do not insert the curly brackets on your own (excel will do that).

With the example code below I got the correct match:

{=IFERROR(INDEX($J$2:$J$9, MATCH($B2 & $D2, $E$2:$E$9 & $G$2:$G$9),0), 0)}