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

 

[Guest]

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:

 

[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)

 

[ramana]

Hi,

Thank you all for giving solution to yhe problem

Regards

Ramana

 

[Guest]

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

Regards,
Stefi


„Bob Phillips†ezt írta: