Index Match Question

bhall

New Member
Joined
Sep 27, 2013
Messages
12
I feel like this should be easy, but it is not working out for me so far. Basically, I have two worksheets, and I need to import data from the second spreadsheet when the two other columns match across worksheets.

Basic setup is this:
When the values in columns b and c match on both Spreadsheet 1 (SS1) and Spreadsheet 2 (SS2), I want to populate the value from column a on SS2 into column d on SS1. While columns b and c will match on both spreadsheets, they are not listed in the same order, so I need some type of array function to actually find the correct row on SS2.

Additional Info:
Not sure if it matters, but the values being referenced on SS1 (columns b and c) are contained within a pivot table

Thanks for the assistance
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi, what formula are you presently using?
 
Upvote 0
INDEX(CurrentPO!$A:$R,MATCH(1,(CurrentPO!$N:$N=CurrentMat!E5)*(CurrentPO!$L:$L=GETPIVOTDATA("Direct",CurrentMat!$E$3,"JOB #","28519-0004","Resource","MATL")),0),1)

When translated into my example above would look like:

index(range on SS2,match(1,(column b on SS1=column b on SS2)*(column c on SS1=column c on SS2)),0),1)

I doubt this is correct, but it reflects about the 5th formula revision I have tried to make this work.
 
Upvote 0
Have you tried NOT using whole column references?
Why not just '=INDEX(CurrentPO!$A$1:$R$1000,MATCH(CurrentMat!E5,CurrentPO!$N$1:$N$1000,0),MATCH(GETPIVOTDATA("Direct",CurrentMat!$E$3,"JOB #","28519-0004","Resource","MATL"),CurrentPO!$L$1:$L$1000,0))
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top