# Index Match with 4 columns

i have excel file with 2 sheets, one sheet contents my items, prices, codes ,etc. the other sheet is to do cross matching with competitors. i've included an excel file and image to make my question as simple and clear.

i want to be able to generate my code automatically when manually entering any of my competitors codes. i was able to do index match but i was able to match with one column only as following (i'm assuming they all in one sheet to make it easier: =INDEX(C:C,MATCH(K2,E:E,0))

so this is looking only in E:E, when tried to enter different column such as C:C or D:D, etc. it returns error always.

tried to do the match as C:G but it gave error right away!

can anyone help please? would greatly appreciate.

This formula, filled down from B8 in the screenshot below, finds the "My Code" that corresponds to any Competitor Code:

`=INDEX(B\$2:B\$5,SUMPRODUCT((C\$2:G\$5=A8)*ROW(C\$2:G\$5))-1)`

How it works: The expression `(C\$2:G\$5=A8)` is an array with `True` where the array of Competitor codes equals the one in A8, and `False` everywhere else.`ROW(C\$2:G\$5)` is an array of all the row numbers of the Competitor codes. Multiplying these two arrays gives an array with the row number of the code in A8 in the position where the first array was `True`, and 0 everywhere else.

`SUMPRODUCT()` sums that array, giving just the row number. That's used as the `row_num` (subtracting 1 to account for the header row) in an `INDEX()` formula to return the matching My Code.

I hope this helps and good luck.

• thank you very very much for your help.. i do appreciate – Sam Kay Apr 25 '18 at 14:19

I think the answer to this question depends on how you want to use your codes table.

Suppose the first row of your table (row 2) is completely empty, and then you enter "Code 52" into C2. You then want B1 to show "My Code 2". But then, what happens if you enter a different code into E2, for example? How would you want cell B1 to change?

If only one of the competitor code columns (C through G) will have a value in each row, you could do this with a bunch of nested IF statements:

For B2: `=IF(C2<>"", INDEX(...), IF(D2<>"", INDEX(...), ... ))`