software

Hi,

I would like to see any excel formula (vlookup/ index amp; match) can help to
return the desired value:See below -

appleshop A2/15/2006 ??? lt; ---- (to be returned as 0.5)
appleshop B3/27/2006 ??? lt; ---- (to be returned as 0.6)Lookup table:
ABCDE
appleshop A1/1/20062/28/20060.5
appleshop A3/1/20065/30/20060.7
appleshop B1/1/20061/2/20061
appleshop B1/3/20063/28/20060.6thanksThanx Max, it works

quot;Maxquot; wrote:

gt; Assuming the source data is in sheet: X, cols A to E, within rows 1 - 10
gt;
gt; and in sheet: Y,
gt; you have the set-up in row1 down, cols A to C:
gt;
gt; gt; appleshop A2/15/2006 ??? lt; ---- (to be returned as 0.5)
gt; gt; appleshop B3/27/2006 ??? lt; ---- (to be returned as 0.6)
gt;
gt; Put in D1, array-enter (press CTRL SHIFT ENTER):
gt; =INDEX(X!$E$1:$E$10,MATCH(1,(X!$A$1:$A$10=A1)*(X!$ B$1:$B$10=B1)*(C1gt;=X!$C$1:$C$10)*(C1lt;=X!$D$1:$D$10 ),0))
gt; Copy D1 down
gt;
gt; Adapt to suit ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Catherinequot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; I would like to see any excel formula (vlookup/ index amp; match) can help to
gt; gt; return the desired value:
gt; gt;
gt; gt;
gt; gt; See below -
gt; gt;
gt;
gt; gt;
gt; gt;
gt; gt; Lookup table:
gt; gt; ABCDE
gt; gt; appleshop A1/1/20062/28/20060.5
gt; gt; appleshop A3/1/20065/30/20060.7
gt; gt; appleshop B1/1/20061/2/20061
gt; gt; appleshop B1/3/20063/28/20060.6
gt; gt;
gt; gt;
gt; gt; thanks
gt; gt;
gt; gt;
gt; gt;
gt; gt;

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:

gt; appleshop A2/15/2006 ??? lt; ---- (to be returned as 0.5)
gt; appleshop B3/27/2006 ??? lt; ---- (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)*(C1gt;=X!$C$1:$C$10)*(C1lt;=X!$D$1:$D$10 ),0))
Copy D1 down

Adapt to suit ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Catherinequot; wrote:

gt; Hi,
gt;
gt; I would like to see any excel formula (vlookup/ index amp; match) can help to
gt; return the desired value:
gt;
gt;
gt; See below -
gt;

gt;
gt;
gt; Lookup table:
gt; ABCDE
gt; appleshop A1/1/20062/28/20060.5
gt; appleshop A3/1/20065/30/20060.7
gt; appleshop B1/1/20061/2/20061
gt; appleshop B1/3/20063/28/20060.6
gt;
gt;
gt; thanks
gt;
gt;
gt;
gt;