Return data after vlookup and condition - macro?

[FireGeek21]

I have 2 Excel files and I need to move some data from one file to the other based on a value and other conditions. I have done vlookups and if formulas before but I believe this will entail more - maybe a macro.

In my main file, I have a column with values I need to match with the first column in a data table (sample pictured below). Then, if a date in my main file is in the date range of the next two columns, then return the data in the 4th column to the main table. So, for my example, for A I want 789 and for B I want 123 into the 3rd column of the main table. HOW???

Main Table
A 03/15/2012
B 04/02/2010

Data Table
A 01/01/2010 06/30/2011 123
A 07/01/2011 12/31/2011 456
A 01/01/2012 08/31/2012 789
A 09/01/2012 12/31/2013 001
B 01/01/2010 06/30/2011 123
B 07/01/2011 12/31/2011 456
B 01/01/2012 08/31/2012 789
B 09/01/2012 12/31/2013 001

So, long formula? Macro? HOW??? THANKS!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[SkipVought]

hi,

What! No headings on your table???

Make sure both workbooks are open.
[tt]
=SUMPRODUCT((DataTableColumnnA_Ref=A1)*(DataTableColumnnB_Ref<=B1)*(DataTableColumnnC_Ref>B1)*(DataTableColumnnD_Ref))
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[FireGeek21]

Not sure SUMPRODUCT will work here. All my data is text or dates. I think you missed the part where the data in column 4 of the data table needs to be moved to column 3 of the main table.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[zelgar]

Did you try Skip's formula in Col 3? You probably need to modify the DataTableColumnA_Ref to something like '[filename.ext]Sheetname"!A:A and the others in order for it to work.

 

[SkipVought]

I used your test data and it works!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[FireGeek21]

I used Skip's formula and I get 0 returned for the first several rows. What am I doing wrong? Seems it isn't looking through the data table (paposition.xls) to find the row that matches first E2 AND <= F2 and > F2.

=SUMPRODUCT(([paposition.xls]Sheet1!$B$2=E2)*([paposition.xls]Sheet1!$E$2<=F2)*([paposition.xls]Sheet1!$F$2>F2)*([paposition.xls]Sheet1!$G$2))

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[SkipVought]

[tt]=SUMPRODUCT(([paposition.xls]Sheet1!B:B=E2)*([paposition.xls]Sheet1!E:E<=F2)*([paposition.xls]Sheet1!F:F>F2)*([paposition.xls]Sheet1!G:G))[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[FireGeek21]

UGH, now I get #VALUE!

=SUMPRODUCT(([paposition.xls]Sheet1!B:B=E2)*([paposition.xls]Sheet1!E:E<=F2)*([paposition.xls]Sheet1!F:F>F2)*([paposition.xls]Sheet1!G:G))

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[zelgar]

I noticed that you forgot to have the ' before the filename and the !. Also, you'd probably want use the data if it is equal to the ending date as well.

=SUMPRODUCT(('[paposition.xls]Sheet1'!B:B=E2)*('[paposition.xls]Sheet1'!E:E<=F2)*('[paposition.xls]Sheet1'!F:F=>F2)*('[paposition.xls]Sheet1'!G:G))

if that doesn't work...

=SUMPRODUCT(('[paposition.xls]Sheet1'!A:A=A2)*('[paposition.xls]Sheet1'!C:C<=B2)*('[paposition.xls]Sheet1'!D=>B2)*('[paposition.xls]Sheet1'!E:E))

Based upon your original post, the following information was in the tables

Column     Data Table      Main Table
   A          A or B         A or B
   B       Starting Date   Querried Date
   C       Ending Date     Place for formula to get data from Data Table
   D       Value

If the data is different from above, then you need to change the above formula. If the change is in the Data Table, Change the appropriate location in the formula (e.g., If the Starting Date is in Col E, '[paposition.xls]Sheet1'!A:A to '[paposition.xls]Sheet1'!E:E). If the Main Table is in a different then change the value after the =

 

[FireGeek21]

Thinking this has to do with the data in a different file, I moved it to a sheet in my main file (sheet1). I am still getting #VALUE!

=SUMPRODUCT((Sheet1!B:B=E2)*(Sheet1!E:E<=F2)*(Sheet1!F:F>=F2)*(Sheet1!G:G))

Could the problem be with the fact that my data is actually text/dates and not numbers?

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[SkipVought]

the foolproof method of entering with minimum typing is:

ENTER the [highlight #FCE94F]=[/highlight]

Start typing the function and then select and TAB from the context sensitive popup: if you were doing the SUMPRODUCT() function you would now have [highlight #FCE94F]=SUMPRODUCT([/highlight] in your cell.

enter the [highlight #FCE94F]([/highlight] for the first expression

then point to the workbook/worksheet/range which in this case would be an entire column. ALL THE PUNCTUATIONS AND SYNTAX WILL BE AS REQUIRED.

enter the [highlight #FCE94F]=[/highlight] and point to the cell in the first column, enter the [highlight #FCE94F])[/highlight] to bound the first expression.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

YES, if the format of the data is TEXT then you're not going to be able to compare < and >. Using the VALUE function will allow you to convert the TEXT into a number.

if the data table is TEXT
=SUMPRODUCT((VALUE('[paposition.xls]Sheet1'!B:B)=E2)*(VALUE('[paposition.xls]Sheet1'!E:E)<=F2)*(VALUE('[paposition.xls]Sheet1'!F:F)=>F2)*(VALUE('[paposition.xls]Sheet1'!G:G)))

If the Main Table is TEXT
=SUMPRODUCT(('[paposition.xls]Sheet1'!B:B=VALUE(E2))*('[paposition.xls]Sheet1'!E:E<=VALUE(F2))*('[paposition.xls]Sheet1'!F:F=>VALUE(F2))*('[paposition.xls]Sheet1'!G:G))

if both Tables are TEXT
=SUMPRODUCT((VALUE('[paposition.xls]Sheet1'!B:B)=VALUE(E2))*(VALUE('[paposition.xls]Sheet1'!E:E)<=VALUE(F2))*(VALUE('[paposition.xls]Sheet1'!F:F)=>VALUE(F2))*(VALUE('[paposition.xls]Sheet1'!G:G)))

 

[FireGeek21]

This just isn't working! I get a 0 with this formula (both tables are TEXT).

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[SkipVought]

Both tables are TEST."

Are you absolutely sure?

If you change the Number Format on any DATE value to GENERAL and nothing caenges in the cell display and nothing changes, then you have TEXT. Otherwise that value is NUMERIC formatted as DATE.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}