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. Example image


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


enter image description here

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(...), ... ))

To give a better answer we'll need to know more about your problem.

  • Hi mike and thanks for your quick response. i – Sam Kay Apr 24 '18 at 18:21
  • Hi mike and thanks for your quick response. i was thinking of your suggestion, but it wont work for me because i can't assume the value of C2, D2, etc. I want to be able to match my code with all other suppliers. example: if i have an item and suppose its a computer mouse, my code is ABC, and i have 2 other competitors who are selling same item with different prices, assume that first competitor code for the same item is DEF and the other competitor code is GHI for the same item. all i want is to get my code "ABC" whenever i enter DEF or GHI. – Sam Kay Apr 24 '18 at 18:29
  • whenever i have any code from any supplier, i want to enter it and know my matching code for the very same item. "Knowing that i have a table contains my codes against competitors code" i just need to get them in a different sheet when i search for a particular code. hope that was clear and sorry for any inconvenience – Sam Kay Apr 24 '18 at 18:29

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.