Using INDEX / IF / MATCH Function between two workbooks

DavidHaley

New Member
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.

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

Replies
5
Views
45
Replies
11
Views
191
Replies
2
Views
442
Replies
13
Views
82
Replies
8
Views
120

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.

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