Using INDEX / IF / MATCH Function between two workbooks

[DavidHaley]

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

 

[venkat1926]

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]

I had success with the following entered as an array formula in cell E2 of the destination sheet:

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

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]

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]

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