How to return a value Referring to the other cell.

[ramana]

Hi Everbody,

I have three columns A,B,C First column is having numbers
sequential numbers 1,2,3,4....n and the second column is having time
for that sequential numbers 8:00, 8:02,8:05,8:07,8:11,........ n column
C I have timings 8:00,8:05,8:10,8:15,8:20... Now in Column D I need to
have a formula such that it returns me a value from column A
corresponding to column C, Some times, This can be done by lookup
formula, but the problem is for some values in C you will not find in B
un that case the next value should be displayrd. Here I'm Giving an
example for better understading.


A B C D(Formula column)
1 8:01 8:00 1(as there is no 8:00 in B it goes to the
immediate next value)
2 8:03 8:05 3(for 8:05 there is a corresponding value
in A)
3 8:05 8:10 5(as there is no 8:10 in B it goes to the
immediate next value)
4 8:08 8:15 6(as there is no 8:15 in colun B it goes
to the immediate next value)
5 8:11
6 8:16
...
..
..
Any suggestions to solve this problem.

Thanks and Regards

Ramana

[damorrison]

couldn't you just put the formula in column D
=C1-B1
you may have to format column D be that should be no problem!

I may not understand your question,
lets see what your formula looks like!

[ramana]

My formula lokks like this.

D1=lookup($C1,$B$1:$B$10,$A$1:$A$$10)

but the problem is it is returning me the cell value which is less thsn
that I'm looking for In the above example, for 8:10 in column C it
should return me a value 5 but it is returning 4. I think now you can
understand the problem.

Thanks and Regards

Ramana

[Stefi]

Hi Ramana,

Apply a dummy first row in your table with B1=0:00
and try this in D2:
=IF(ISERROR(VLOOKUP($C2,$B$1:$B$10,1,FALSE)),LOOKUP($C2,$B$1:$B$10,$A$1:$A$10)+1,LOOKUP($C2,$B$1:$B$10,$A$1:$A$10))

Regards,
Stefi

„ramana” ezt �*rta:

> My formula lokks like this.
>
> D1=lookup($C1,$B$1:$B$10,$A$1:$A$$10)
>
> but the problem is it is returning me the cell value which is less thsn
> that I'm looking for In the above example, for 8:10 in column C it
> should return me a value 5 but it is returning 4. I think now you can
> understand the problem.
>
> Thanks and Regards
>
> Ramana
>
>

[Bob Phillips]

Surely better to use

MATCH($C2,$B$1:$B$10,0)

than

VLOOKUP($C2,$B$1:$B$10,1,FALSE)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" <[email protected]> wrote in message
news:[email protected]...
> Hi Ramana,
>
> Apply a dummy first row in your table with B1=0:00
> and try this in D2:
>
=IF(ISERROR(VLOOKUP($C2,$B$1:$B$10,1,FALSE)),LOOKUP($C2,$B$1:$B$10,$A$1:$A$1
0)+1,LOOKUP($C2,$B$1:$B$10,$A$1:$A$10))
>
> Regards,
> Stefi
>
> "ramana" ezt �rta:
>
> > My formula lokks like this.
> >
> > D1=lookup($C1,$B$1:$B$10,$A$1:$A$$10)
> >
> > but the problem is it is returning me the cell value which is less thsn
> > that I'm looking for In the above example, for 8:10 in column C it
> > should return me a value 5 but it is returning 4. I think now you can
> > understand the problem.
> >
> > Thanks and Regards
> >
> > Ramana
> >
> >

[ramana]

Hi,

Thank you all for giving solution to yhe problem

Regards

Ramana

[Stefi]

Yes, I see, one can always learn better solutions!

Regards,
Stefi


„Bob Phillips” ezt �*rta:

> Surely better to use
>
> MATCH($C2,$B$1:$B$10,0)
>
> than
>
> VLOOKUP($C2,$B$1:$B$10,1,FALSE)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Stefi" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Ramana,
> >
> > Apply a dummy first row in your table with B1=0:00
> > and try this in D2:
> >
> =IF(ISERROR(VLOOKUP($C2,$B$1:$B$10,1,FALSE)),LOOKUP($C2,$B$1:$B$10,$A$1:$A$1
> 0)+1,LOOKUP($C2,$B$1:$B$10,$A$1:$A$10))
> >
> > Regards,
> > Stefi
> >
> > "ramana" ezt �*rta:
> >
> > > My formula lokks like this.
> > >
> > > D1=lookup($C1,$B$1:$B$10,$A$1:$A$$10)
> > >
> > > but the problem is it is returning me the cell value which is less thsn
> > > that I'm looking for In the above example, for 8:10 in column C it
> > > should return me a value 5 but it is returning 4. I think now you can
> > > understand the problem.
> > >
> > > Thanks and Regards
> > >
> > > Ramana
> > >
> > >
>
>
>