Lookup Value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Catherine - 20 Mar 2006 15:50 GMT

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

Reply to this Message

Max - 20 Mar 2006 16:01 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 4 lines]
>
> thanks

Reply to this Message