INDEX or VLOOKUP?

[marshak]

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

Thank you in advance!!

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

 

[steve the fish]

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.

 

[marshak]

Okay, I will try that now and let you know. Thanks!



Also posted in following forum:
https://www.excelforum.com/excel-formulas-and-functions/1260744-lookup-and-fill-in-data.html