Developer Forum

I wish your advice...
My formula result ERROR , I thing VLOOKUP function can not meet the expected
condition (APPLY DATE<=C1<=END DATE).
Do you have any kind of solution for this case?

A B C D
1 MAIN DATA ENTER DATE: 20041215-->constant
2 PART NO PRICE
3 1A ??? ==>Formula=IF(AND($A1=VLOOKUP($A1;
4 1B ??? A7:C10;1;FALSE);
$C$1>=VLOOKUP($A1;A7:C10;
2;FALSE);$C$1<=VLOOKUP
($A1;A7:C10;3;FALSE));
VLOOKUP($A1;A7:C10;4);
"ERROR")
5 SOURCE DATA
6 PART NO APPLY DATE END DATE PRICE (USD)
7 1A 20010101 20031201 1.5
8 1A 20031202 99991201 1.0
9 1B 20010101 20040501 5.0
10 1B 20040502 99991231 6.0

Regards,
Oso

One interp / way to try:

Assuming ..

The "ENTER" date entered in C1 is: 15-Dec-04

and you have listed the part#s

in A3: 1A
in A4: 1B

Assume the reference data below is in A7:D10

1A 01-Jan-01 01-Dec-03 1.5
1A 02-Dec-03 01-Dec-99 1
1B 01-Jan-01 01-May-04 5
1B 02-May-04 31-Dec-99 6

Note: I used Data > Text to Columns** on the "dates" in B7:B10 and C7:C10
(in turn) to convert the original data posted into "real" dates

**Steps:
[ Select the range (say B7:B10),
click Data > Text to Columns,
click Next > Next,
In Step 3 of the wiz.
under "Column data format:"
Check "Date" and select "YMD" from the droplist,
click Finish ]

Put in B3:

=IF(ISNA(MATCH(1,($A$7:$A$10=A3)*($B$7:$B$10<=C$1)*($C$7:$C$10>=C$1),0)),"",
INDEX($D$7:$D$10,MATCH(1,($A$7:$A$10=A3)*($B$7:$B$10<=C$1)*($C$7:$C$10>=C$1)
,0)))

Array-enter the formula in B2,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Copy B3 down to B4

B3 will return 1, B4 returns 6
(which is what you're after ?)

Blanks "" will be returned for any unmatched cases

Think you'd need to replace the commas with semicolons
in the formula to suit your excel language ..

Sumproduct

Biff

B7:B10 and C7:C10
dates
($C$7:$C$10>=C$1),0)),"",
*($C$7:$C$10>=C$1)

not meet the> >expected>>>> condition (APPLY DA<E<=<1<=END DATE).>>>> Do you have any kind of solution for this case?>>>>>>>> A B C D>>>> 1 MAIN DATA ENTER DATE: 20041215>->constant>>>> 2 PART NO PRICE>>>> 3 1A ??? >=>Formula=IF(AND($A1=VLOOKUP
($A1;>>>> 4 1B ??? A7:C10;1;FALSE);>>>> $C>1>=VLOOKUP
($A1;A7:C10;>>>>
2;FALSE);$C<1<=VLOOKUP>>>>
($A1;A7:C10;3;FALSE));>>>> VLOOKUP
($A1;A7:C10;4);>>>> "ERROR")>>>> 5 SOURCE DATA>>>> 6 PART NO APPLY DATE END DATE PRICE (USD)>>>> 7 1A 20010101 20031201 1.5>>>> 8 1A 20031202 99991201 1.0>>>> 9 1B 20010101 20040501 5.0>>>> 10 1B 20040502 99991231 6.0>>>>>>>> Regards,>>>> Oso>>>>>>>>> >> >> >.> >