INDEX or VLOOKUP?

marshak

Board Regular
I need help! I have been frustrated with this for days now. Hopefully someone can help me through this.

Here's what I have to do:

First thing....
I have an Excel sheet of about 500,000 rows of data (Data). I have another Excel sheet with roughly 200 rows of data (Matrix). I need to look up the PART# found in A2 of Matrix and see if it occurs in any of the 500,000 rows of column A of Data.

Second thing...
If that PART# found in column A of Matrix is found in column A of Data, I need to extract the data from Data (DATE_APPLIED, QUANTITY, Cost) and place them into the same named columns found in Matrix.

I have placed the following formula in cell B2, copied it across to cell D2, then down the rows...but it has not worked. Just gives me #N/A

=INDEX('Data'!B2:D449112,MATCH(\$A2,'Data'!A2:A449112,0),MATCH(B\$1,'Data'!B1:D1,0))

What am I doing wrong? Am I using the best function to accomplish what I need to?

Here are a few columns, rows from sheet, Matrix
COLUMN A COLUMN B COLUMN C COLUMN D
PART# DATE_APPLIED QUANTITY Cost

127217 #N/A #N/A #N/A
127217x #N/A #N/A #N/A
128903 #N/A #N/A #N/A
129895 #N/A #N/A #N/A
131137 #N/A #N/A #N/A

Here are a few columns, rows from sheet Data
COLUMN A COLUMN B COLUMN C COLUMN D
PART DATE_APPLIED QUANTITY Cost

14C58HWSERPH 01/03/06 30000 0.00971
1023 01/03/06 7000 0.0058
50149 01/03/06 105000 0.003
54679 01/03/06 693500 0.00244
85415 01/03/06 18000 0.02868
119937 01/03/06 22000 0.00275
121366 01/03/06 20000 0.007
121387 01/03/06 40000 0.0037

Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

steve the fish

Well-known Member
For sure you need to be absoluting your references to your index array table and the match lookup arrays. Try that and see what happens.

Replies
3
Views
242
Replies
0
Views
173
Replies
6
Views
204
Replies
6
Views
196
Replies
7
Views
418

1,195,970
Messages
6,012,609
Members
441,715
Latest member
TTP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back