vlookup two values and return another one

[deep_101]

Hi All,

First time posting here and im not that familiar with VBA.

I have two workbooks which are data dumps from another program. Say I have WO #, Task #, and Cost in workbook1 and WO #, Task # in workbook2. I need to compare the WO# in workbook2 with WO# in workbook1 if it matches I need to compare the Task # in workbook2 with the Task# in workbook1 (but it must compare the same row in which the WO # match was found) then it must return the cost from workbook1 to workbook2 ( again the cost must come from the same row in which the WO and Task match were found in).

This could be a VBA code or even a formula that does not matter.

Your help would be appreciated.

Thank you.

 

[EverCurious]

Is the WO# to Task# always a 1 to 1 relationship or can there be multiple Task#s for a given WO#? In other words can this condition ever exist:

WO#1 Task#22 $2.15
WO#2 Task#1 $2.01
WO#2 Task#2 $1.15
WO#3 Task#22 $3.35

 

[EverCurious]

Thought about it more....

Regardless of your answer to above, you can also combine the CONCATENATE and VLOOKUP functions to achieve results. For example:

=VLOOKUP(CONCATENATE(A1,B1),Sheet2!$C$1:$D$6,2,FALSE)

If A1 and B1 on Sheet1 correspond to WO# and Task#, and Sheet2 has a column, C, that has a precombined version of WO# and Task# and the cost is in column D. Then you can compare concatenated versions to each other. If you don't like the combined version in Sheet2, just hide the column.

 

[wsjackman]

welcome to the forum.

This sounds like a good place for a sum product.

can you give an example of your data?

 

[deep_101]

Is the WO# to Task# always a 1 to 1 relationship or can there be multiple Task#s for a given WO#? In other words can this condition ever exist:

WO#1 Task#22 $2.15
WO#2 Task#1 $2.01
WO#2 Task#2 $1.15
WO#3 Task#22 $3.35

Yes it is possible to have multiple tasks under one WO, but probably not more than 2.

 

[deep_101]

Thought about it more....

Regardless of your answer to above, you can also combine the CONCATENATE and VLOOKUP functions to achieve results. For example:

=VLOOKUP(CONCATENATE(A1,B1),Sheet2!$C$1:$D$6,2,FALSE)

If A1 and B1 on Sheet1 correspond to WO# and Task#, and Sheet2 has a column, C, that has a precombined version of WO# and Task# and the cost is in column D. Then you can compare concatenated versions to each other. If you don't like the combined version in Sheet2, just hide the column.

So to give you a bit more history, I originally have 2 workbooks that are generated reports from another program. So therefore I cant have any macros in these files because they will be replaced frequently. I have created another workbook that when opened will automatically copy the data from one of the two generated workbooks. Now once I have copied data into this new work book I want it two check two columns (WO# and Task #) with the second workbook and if both of them match (in the same row) then I want it to give me back the cost column. Hopefully that clarifies some stuff. And because of this I cant have a precombined column in the second workbook. And also thank you guys a lot I really do appreciate your help

 

[wsjackman]

assuming you have your data in column A&B in wb1 sheet 1 and columns ABC in wb2 sheet1 boith starting in row 2

In c2 of WB1 sheet 1

=sumproduct(('[wb2]sheet1'!A$1:A$1000=a2)*('[wb2]sheet1'!B$1:B$1000=B2)*'[wb2]sheet1'!C$1:C$1000)

adjust ranges, workbook names and sheet names as needed.

 

[deep_101]

welcome to the forum.

This sounds like a good place for a sum product.

can you give an example of your data?

Workbook1:

WO# Task# Planned Cost
1 1 1.00
2 1 34.00
3 1 2.00
3 2 40.00
4 4 45.00


Workbook2"
WO# Task# Actual Cost
1 1 11.00
2 1 24.00
3 2 10.00
7 2 40.00
9 4 47.00


I need to have Workbook1 compare WO# and Task# if both of them match I need to bring back the Actual Cost. Note that both of these workbooks contain other columns as well and also that I need to show everything in Workbook1 so that will be the main workbook that data will be copied to.

 

[wsjackman]

if you want the actual cost in column D change the location of the formulas in my previous post.

 

[deep_101]

if you want the actual cost in column D change the location of the formulas in my previous post.

Ok that seems to work fine, thank you a lot your help is appreciated.

Cheers