Using INDEX / IF / MATCH Function between two workbooks

DavidHaley

New Member
Joined
Jun 14, 2012
Messages
3
Good afternoon everyone,

I am hoping someone can help... I have a source workbook (report) that I download every day which contains the following three columns as well as two columns containing open/close dates for work orders. I don't have any questions regarding the date columns, but I thought I would mention them just incase.

I also created a destination work book containing all of my fleet's unit numbers. I am looking to pull the latest mileage for each unit number from the sourcebook, for each repair type (there are two). I am familiar with VLOOKUP but I realized I couldn't use this for looking up two columns and returning the value from a third. After doing some research, I found and edited an INDEX/MATCH/IF formula but I can't seem to get it working.

Columns that matter are in Bold.

Source Workbook Format:

Column A Column B Column CColumn D Column E
Unit Mileage Work Order Open Date Work Order Close Date Repair Type (100 = B / 101 = A)

Ex; 240-1000 Ex; 100000 Ex; 06/04/2012 06/05/2012 05-000100
Ex; 240-1001 Ex; 150000 Ex; 06/07/2012 06/08/2012 05-000101
Ex; 240-1000 Ex; 80000 Ex; 06/04/2012 06/05/2012 05-000101
Ex; 240-1001 Ex; 175000 Ex; 06/07/2012 06/08/2012 05-000100



Destination Workbook Format:


Column A Column B Column C Column D Column E Column F Column G Column H
Unit Customer Customer Unit # VIN Last PMA Service (101) Last PMB Service (100) Repair Type A Repair Type B

Ex; 240-1000 - - - 80,000 100,000 05-000101 05-000100
Ex; 240-1001 - - - 150,000 175,000 05-000101 05-000100



My goal is to scan Column A and Column E in the source work book and return the last mileage associated to each repair type from Column B. I've tried using this formula in my destination work book for the repair type B only so far:


=INDEX("[Paclease_Service_Mileage.xlsx]Paclease_Service_Mileage!$B$2:$B$2000",MATCH(A2,IF('L:\Mileage Data\[Paclease_Service_Mileage.xlsx]Paclease_Service_Mileage'!$E$2:$E$2000=H2,'L:\Mileage Data\[Paclease_Service_Mileage.xlsx]Paclease_Service_Mileage'!$A$2:$A$2000),0))


I keep receiving a #VALUE! error. I made sure all of the cells in both work books are not formatted as text and they are formatted as general. I also made sure to press CNTL+ALT+ENTER to make the formula an array. I also tried using LEN to count how many characters there were to make sure there weren't any additional spaces.

If someone could please help me solve this problem it would be greatly appreciated!!!

Kind regards,

David
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
I am giving you a suggestion try this. name the relevant ranges with a short name
use those in the formula
when you refer to named ranges you have to use workbook name
for e.g.
Paclease_Service_Mileage.xlsx!<name of range>

experiment on these lines
 

rob.barnes01

Board Regular
Joined
Aug 26, 2010
Messages
94
I had success with the following entered as an array formula in cell E2 of the destination sheet:

Code:
=INDEX([Paclease_Service_Mileage.xlsx]Paclease_Service_Mileage!$A$2:$E$2000,MATCH(A3&G3,[Paclease_Service_Mileage.xlsx]Paclease_Service_Mileage!$A$2:$A$2000&[Paclease_Service_Mileage.xlsx]Paclease_Service_Mileage!$E$2:$E$2000,0),2)

Hope this helps.

Rob
 

DavidHaley

New Member
Joined
Jun 14, 2012
Messages
3
Wow, thanks Rob! This works! I was wondering is there a way I can incorporate something into the formula to display a blank if nothing is found instead of #N/A?

Thanks again!

David
 

levantkaien

New Member
Joined
Jun 15, 2012
Messages
10
try =iferror(INDEX([Paclease_Service_Mileage.xlsx]Paclease_Service_Mileage!$A$2:$E$2000,MATCH(A3&G3,[Paclease_Service_Mileage.xlsx]Paclease_Service_Mileage!$A$2:$A$2000&[Paclease_Service_Mileage.xlsx]Paclease_Service_Mileage!$E$2:$E$2000,0),2),"")
 

DavidHaley

New Member
Joined
Jun 14, 2012
Messages
3
Nevermind I got it by using an IF(ISNA( formula. Thanks again to you both!

=IF(ISNA(INDEX(Paclease_Service_Mileage.xlsx!$A$2:$E$2000,MATCH(A2&I2,Paclease_Service_Mileage.xlsx!$A$2:$A$2000&Paclease_Service_Mileage.xlsx!$E$2:$E$2000,0),2)),"",INDEX(Paclease_Service_Mileage.xlsx!$A$2:$E$2000,MATCH(A2&I2,Paclease_Service_Mileage.xlsx!$A$2:$A$2000&Paclease_Service_Mileage.xlsx!$E$2:$E$2000,0),2))

David
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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