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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi, what formula are you presently using?
 

bhall

New Member
Joined
Sep 27, 2013
Messages
12
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.
 

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

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
Top