Lookup Help

[Brento]

HI,

I need assistance with this query. I have a Table of data displayed below
a1 b1 c1 d1
13/08/06 14/08/06 15/08/06
08:30 24 123 233
08:45 78 46 176
09:00 98 122 322
09:15 101 146 244

I need a formula to say if date displayed in e1 which will be a manual date in put and e2 which will be a manual time input but varys around and not a spot on 15 minute time. then display value in the cell.

Example e1= 14/08/06 e2= 09:01 e3 (the result) = 122.

I think it is a vllokup formula but not sure how to put it all in 1 cell for that 3 day period of data.

thanks in advance
Brento

[Biff]

Hi!

As long as the times listed in column A are in ascending order (like they
are in your sample):

=IF(COUNT(E1,E2)=2,VLOOKUP(E2,A2:D5,MATCH(E1,A1:D1,0)),"")

Any time criteria <8:30 will return #N/A.
Any time criteria >9:15 will return the last value from the corresponding
column.

Biff

"Brento" <Brento.2cg7bu_1155421509.0635@excelforum-nospam.com> wrote in
message news:Brento.2cg7bu_1155421509.0635@excelforum-nospam.com...
>
> HI,
>
> I need assistance with this query. I have a Table of data displayed
> below
> a1 b1 c1 d1
> 13/08/06 14/08/06 15/08/06
> 08:30 24 123 233
> 08:45 78 46 176
> 09:00 98 122 322
> 09:15 101 146 244
>
> I need a formula to say if date displayed in e1 which will be a manual
> date in put and e2 which will be a manual time input but varys around
> and not a spot on 15 minute time. then display value in the cell.
>
> Example e1= 14/08/06 e2= 09:01 e3 (the result) = 122.
>
> I think it is a vllokup formula but not sure how to put it all in 1
> cell for that 3 day period of data.
>
> thanks in advance
> Brento
>
>
> --
> Brento
> ------------------------------------------------------------------------
> Brento's Profile:
> http://www.excelforum.com/member.php...o&userid=25213
> View this thread: http://www.excelforum.com/showthread...hreadid=571085
>

[RagDyeR]

Try this:

=INDEX(B2:D5,MATCH(E2,A2:A5),MATCH(E1,B1:D1,0))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Brento" <Brento.2cg7bu_1155421509.0635@excelforum-nospam.com> wrote in
message news:Brento.2cg7bu_1155421509.0635@excelforum-nospam.com...

HI,

I need assistance with this query. I have a Table of data displayed
below
a1 b1 c1 d1
13/08/06 14/08/06 15/08/06
08:30 24 123 233
08:45 78 46 176
09:00 98 122 322
09:15 101 146 244

I need a formula to say if date displayed in e1 which will be a manual
date in put and e2 which will be a manual time input but varys around
and not a spot on 15 minute time. then display value in the cell.

Example e1= 14/08/06 e2= 09:01 e3 (the result) = 122.

I think it is a vllokup formula but not sure how to put it all in 1
cell for that 3 day period of data.

thanks in advance
Brento


--
Brento
------------------------------------------------------------------------
Brento's Profile:
http://www.excelforum.com/member.php...o&userid=25213
View this thread: http://www.excelforum.com/showthread...hreadid=571085