Hi,

I would like to see any excel formula (vlookup/ index & match) can help to
return the desired value:


See below -

apple shop A 2/15/2006 ??? < ---- (to be returned as 0.5)
apple shop B 3/27/2006 ??? < ---- (to be returned as 0.6)


Lookup table:
A B C D E
apple shop A 1/1/2006 2/28/2006 0.5
apple shop A 3/1/2006 5/30/2006 0.7
apple shop B 1/1/2006 1/2/2006 1
apple shop B 1/3/2006 3/28/2006 0.6


thanks

Thanx Max, it works

"Max" wrote:

Assuming the source data is in sheet: X, cols A to E, within rows 1 - 10

and in sheet: Y,
you have the set-up in row1 down, cols A to C:

apple shop A 2/15/2006 ??? < ---- (to be returned as 0.5)
apple shop B 3/27/2006 ??? < ---- (to be returned as 0.6)

Put in D1, array-enter (press CTRL+SHIFT+ENTER):
=INDEX(X!$E$1:$E$10,MATCH(1,(X!$A$1:$A$10=A1)*(X!$ B$1:$B$10=B1)*(C1=X!$C$1:$C$10)*(C1<=X!$D$1:$D$10 ),0))
Copy D1 down

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Catherine" wrote:

Hi,

I would like to see any excel formula (vlookup/ index & match) can help to
return the desired value:


See below -




Lookup table:
A B C D E
apple shop A 1/1/2006 2/28/2006 0.5
apple shop A 3/1/2006 5/30/2006 0.7
apple shop B 1/1/2006 1/2/2006 1
apple shop B 1/3/2006 3/28/2006 0.6


thanks

Assuming the source data is in sheet: X, cols A to E, within rows 1 - 10

and in sheet: Y,
you have the set-up in row1 down, cols A to C:

apple shop A 2/15/2006 ??? < ---- (to be returned as 0.5)
apple shop B 3/27/2006 ??? < ---- (to be returned as 0.6)

Put in D1, array-enter (press CTRL+SHIFT+ENTER):
=INDEX(X!$E$1:$E$10,MATCH(1,(X!$A$1:$A$10=A1)*(X!$ B$1:$B$10=B1)*(C1=X!$C$1:$C$10)*(C1<=X!$D$1:$D$10 ),0))
Copy D1 down

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Catherine" wrote:

Hi,

I would like to see any excel formula (vlookup/ index & match) can help to
return the desired value:


See below -




Lookup table:
A B C D E
apple shop A 1/1/2006 2/28/2006 0.5
apple shop A 3/1/2006 5/30/2006 0.7
apple shop B 1/1/2006 1/2/2006 1
apple shop B 1/3/2006 3/28/2006 0.6


thanks