Index+Match 2 values in multiple columns

Hi team,

I am trying to index + match 2 values in a set of columns for a third value.

My data looks like this (can't share the original file sorry):

Column A / column B / column c/ column d
Rep A / apples / tomatoes /country A / country C
Rep B / apples / tomatoes /country B / country D
Rep C / coconuts / bananas /country A / country D
Rep D / coconuts / tomatoes / --------- /country E


the information is all in a line (like above) and i need to put in a table where i have as a header the Country and on the rows the fruits. This needs to fill up with the name of the rep that sells in that country for each of the fruits.
in my original file i have like 23 fruits and 49 countries covered by only 7 reps. My goal is to have this graphic table to spot which combination of fruit and country is not covered.

like:

Column A / Column B / Column C / Column D / column E
////////// country A / Country B / Country C / Country D / country E
Apples Rep A / Rep B / Rep A
Tomatoes ------- / Rep B / ----------- / Rep B /Rep D
Coconuts Rep C / ------------ / ----------- / Rep C
Bananas

what i have tried was using index + match but because i have to do a combination of country + fruit in order to get the rep that sells them.

{=INDEX(Sheet1!$A:$A,MATCH(1,(Sheet1!$B$1:$c$4 (the fruit ranges) =$b$1 (the fruit Name))*(Sheet1!$c$1:$d$4 (the country ranges) =a$2 (the country name),0))}

I am guessing it is not working because the match does not look properly in the 2 ranges and doesn't check all the combinations available in those columns and ranges ... therefor i'm stuck.


Please let me know if you need more information.

Kind regards,
Beginer

Hi,

yes sure.

please note the table at the back (final form) is not fully filled as for even a few rows it takes a tremendous amount of time

Hi Tony,

I need to add the rep that matches 2 criteria country + fruit in the intersecting cells.
for example:
in my file you have rep E. he covers country X and sells tomatoes and apples. in the final form i need to have an entry in my table for country x column intersecting with apples row . that intersection should contain rep E (because he sells apples in country x).

i have highlighted the above in the file.

Thanks

kind regards,
Beginer

Hi Xor,

I have reattached the file with the Array on. unfortunately same result. (my previous post)

The number of countries and industries is a lot larger and not equal ( i have like 23 industries and 49 countries covered by only 7 reps). i have only sent a part of the file. I can't share the full file, sorry.

my aim was to use the formula from Tony to the full extent of my file. however this is not working even for a small batch


Kind regards,
Beginer

woop!!!

it works!

thank you very much buddy!


kind regards,
Beginer

woop!!!

it works!

thank you very much buddy!


kind regards,
Beginer