Index/Match using two columns

[bjornc]

Hi guys,

i have this rather large data set that contains 80k rows.

The first column is Supplier ID# number, the second column is our internal SKU# pn, and the last column is the MPN# (manufacturing pn). Each SKU# has multiple Suppliers, so you can assume that eg. SKU# 2468037is supplied by Supplier# 02580, X, Y... Z.

Supplier#	SKU#	MPN#
38756	9718122
02580	2468037	4031481
02147	2279467	EFAS-RF-16-400-450-17
03544	8602612	7683
01110	2035805
38756	9716736
03798	9821329	50810
03641	3000104
01110	2034315	700618925
10013	8605888	2600-040-900-000
12081	3257502	4317
03357	2511324	53406120
00742	2187962	22330

I need to make a list that looks something like this:

I have tried: "=INDEX($C$4:$C$61;MATCH($E5;$A$4:$A$61;0);MATCH(F$3;$B$4:$B$61;0))" with no success.

I managed to use an array formula which required me to use CTRL+SHIFT+ENTER, but i was not able to copy it into all the cells.

I am using Excel 2016. My computer is deeply locked into all kinds of secure stuff, i'm not able to install anything.

Thanks for the help in advance.

 

[steve the fish]

Its a bit rude to ignore peoples advice without so much as a comment. Ill leave you to it.

 

[AhoyNC]

As pointed out by Steve the Fish, if your matrix is 80k rows by 80k columns then the formula will probably slow your computer to a crawl.
As you found out you can not just copy and past an array formula in Excel 2016. Once you enter the formula in the first cell of the matrix you then then to DRAG the formula (not copy) to the right and down.
To do this put the cursor in the cell with the formula.
At the bottom right of the cell you will see a little black square.
Move your cursor over this black square and it will turn into a cross hair. Then just drag across and down as needed.