IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP(

G

Guest

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
 
M

Max

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

Biff

Sumproduct

Biff
-----Original Message-----
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 ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Oso said:
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


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top