Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to return a value Referring to the other cell.
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 |
#2
|
|||
|
|||
How to return a value Referring to the other cell.
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! |
#3
|
|||
|
|||
How to return a value Referring to the other cell.
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 |
#4
|
|||
|
|||
How to return a value Referring to the other cell.
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)),LOOKU P($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 |
#5
|
|||
|
|||
How to return a value Referring to the other cell.
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" wrote in message ... 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)),LOOKU P($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 |
#6
|
|||
|
|||
How to return a value Referring to the other cell.
Hi,
Thank you all for giving solution to yhe problem Regards Ramana |
#7
|
|||
|
|||
How to return a value Referring to the other cell.
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" wrote in message ... 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)),LOOKU P($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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return left most part of cell | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
if the value of a cell in a range is not blank, then return the v. | Excel Worksheet Functions | |||
formula to return the value of a cell based on a looked up true reference | Excel Worksheet Functions |