Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
DEAR ALL
I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
Not sure I've understood your question but the formula below returns the
value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
On Feb 14, 6:46 pm, Mike wrote:
Not sure I've understood your question but the formula below returns the value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. THNK YOU MIKE- I will try as u said. What i need is to take the value in one reference cell and look in col-B and get the corresponding value in col-A. onlything is the numbers in col-B are in desending order. Further I also need to take next lower value from col-B and get value from col-A. Then interpolate to get correct value from the two values gotten from col-A for the refrence cell value. Actual scenario is I have tank soundings in col -A; corresponding quantity in col_B. So sometimes i need to see how much is the sounding by using the quantiy which will be in one refrence cell after calculation of required trim. Since the reference value may not be exactly the same as in col-B, i hv to take the two values in col-B and get two values of sounding from col_A and then interpolate for the reference cell. Sorry if i am unable to explain very clearly. Pls advice and once again thnks for ur quick response. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
On Feb 14, 7:15 pm, "CAPTGNVR" wrote:
On Feb 14, 6:46 pm, Mike wrote: Not sure I've understood your question but the formula below returns the value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. THNK YOU MIKE- I will try as u said. What i need is to take the value in one reference cell and look in col-B and get the corresponding value in col-A. onlything is the numbers in col-B are in desending order. Further I also need to take next lower value from col-B and get value from col-A. Then interpolate to get correct value from the two values gotten from col-A for the refrence cell value. Actual scenario is I have tank soundings in col -A; corresponding quantity in col_B. So sometimes i need to see how much is the sounding by using the quantiy which will be in one refrence cell after calculation of required trim. Since the reference value may not be exactly the same as in col-B, i hv to take the two values in col-B and get two values of sounding from col_A and then interpolate for the reference cell. Sorry if i am unable to explain very clearly. Pls advice and once again thnks for ur quick response. YES MIKE I tried as u hv sugested. The problem is it gives #NA if the value looked in col_B is not exact. In my calculations the col_B figures will vary and will not be as in col_B. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
Hi Capt Gnvr:
In the match formula there is an option switch on the end Mike used 0 exact match you can try using -1 or 1. Look at te help pages for match for the exact details of the match. BTW what is Gnvr? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 7:15 pm, "CAPTGNVR" wrote: On Feb 14, 6:46 pm, Mike wrote: Not sure I've understood your question but the formula below returns the value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. THNK YOU MIKE- I will try as u said. What i need is to take the value in one reference cell and look in col-B and get the corresponding value in col-A. onlything is the numbers in col-B are in desending order. Further I also need to take next lower value from col-B and get value from col-A. Then interpolate to get correct value from the two values gotten from col-A for the refrence cell value. Actual scenario is I have tank soundings in col -A; corresponding quantity in col_B. So sometimes i need to see how much is the sounding by using the quantiy which will be in one refrence cell after calculation of required trim. Since the reference value may not be exactly the same as in col-B, i hv to take the two values in col-B and get two values of sounding from col_A and then interpolate for the reference cell. Sorry if i am unable to explain very clearly. Pls advice and once again thnks for ur quick response. YES MIKE I tried as u hv sugested. The problem is it gives #NA if the value looked in col_B is not exact. In my calculations the col_B figures will vary and will not be as in col_B. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
On Feb 14, 8:29 pm, Martin Fishlock
wrote: Hi Capt Gnvr: In the match formula there is an option switch on the end Mike used 0 exact match you can try using -1 or 1. Look at te help pages for match for the exact details of the match. BTW what is Gnvr? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 7:15 pm, "CAPTGNVR" wrote: On Feb 14, 6:46 pm, Mike wrote: Not sure I've understood your question but the formula below returns the value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. THNK YOU MIKE- I will try as u said. What i need is to take the value in one reference cell and look in col-B and get the corresponding value in col-A. onlything is the numbers in col-B are in desending order. Further I also need to take next lower value from col-B and get value from col-A. Then interpolate to get correct value from the two values gotten from col-A for the refrence cell value. Actual scenario is I have tank soundings in col -A; corresponding quantity in col_B. So sometimes i need to see how much is the sounding by using the quantiy which will be in one refrence cell after calculation of required trim. Since the reference value may not be exactly the same as in col-B, i hv to take the two values in col-B and get two values of sounding from col_A and then interpolate for the reference cell. Sorry if i am unable to explain very clearly. Pls advice and once again thnks for ur quick response. YES MIKE I tried as u hv sugested. The problem is it gives #NA if the value looked in col_B is not exact. In my calculations the col_B figures will vary and will not be as in col_B. YESSS MARTIN, placing the -1 in the 'match' worked and thanks. Will you be kind enuff to also tell me if i have to take the next value from the col-B below the referenced value-- is there any way like offset method we use in vb which i can use in excel sheet as formula?? For col_A, I know it increases at regular interval but, for col_B it will be decreasing not steadily. Ahhh by the by GNVR is GADADOSS NAGABUSHANAM VENKAT RAJARAM--lol AND IT IS MY NAME . Thnks Martin and making progress. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
for what I think you want
Interpolation of the values in column A corresponding to the values in column C above and below the Value in C1 try =INDEX(A:A,MATCH(C1,B:B,-1)) -(INDEX(A:A,MATCH(C1,B:B,-1))-(INDEX(A:A,MATCH(C1,B:B,-1)+1))*(INDEX(B:B,MATCH(C1,B:B,-1))-C1)/((INDEX(B:B,MATCH(C1,B:B,-1))-INDEX(B:B,MATCH(C1,B:B,-1)+1)) (hopefully all of parenthesis are corrent) "CAPTGNVR" wrote: On Feb 14, 8:29 pm, Martin Fishlock wrote: Hi Capt Gnvr: In the match formula there is an option switch on the end Mike used 0 exact match you can try using -1 or 1. Look at te help pages for match for the exact details of the match. BTW what is Gnvr? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 7:15 pm, "CAPTGNVR" wrote: On Feb 14, 6:46 pm, Mike wrote: Not sure I've understood your question but the formula below returns the value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. THNK YOU MIKE- I will try as u said. What i need is to take the value in one reference cell and look in col-B and get the corresponding value in col-A. onlything is the numbers in col-B are in desending order. Further I also need to take next lower value from col-B and get value from col-A. Then interpolate to get correct value from the two values gotten from col-A for the refrence cell value. Actual scenario is I have tank soundings in col -A; corresponding quantity in col_B. So sometimes i need to see how much is the sounding by using the quantiy which will be in one refrence cell after calculation of required trim. Since the reference value may not be exactly the same as in col-B, i hv to take the two values in col-B and get two values of sounding from col_A and then interpolate for the reference cell. Sorry if i am unable to explain very clearly. Pls advice and once again thnks for ur quick response. YES MIKE I tried as u hv sugested. The problem is it gives #NA if the value looked in col_B is not exact. In my calculations the col_B figures will vary and will not be as in col_B. YESSS MARTIN, placing the -1 in the 'match' worked and thanks. Will you be kind enuff to also tell me if i have to take the next value from the col-B below the referenced value-- is there any way like offset method we use in vb which i can use in excel sheet as formula?? For col_A, I know it increases at regular interval but, for col_B it will be decreasing not steadily. Ahhh by the by GNVR is GADADOSS NAGABUSHANAM VENKAT RAJARAM--lol AND IT IS MY NAME . Thnks Martin and making progress. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
Hi Gadadoss, (if that is correct)
There is the offset formula in vba or the indirect (which is like cells). The offset is like the OR or AND functions and you do =offset(cell,r,c....) I am not exactly sure what you are looking for so maybe an example would help. Please explain a little more on your requirements. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 8:29 pm, Martin Fishlock wrote: Hi Capt Gnvr: In the match formula there is an option switch on the end Mike used 0 exact match you can try using -1 or 1. Look at te help pages for match for the exact details of the match. BTW what is Gnvr? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 7:15 pm, "CAPTGNVR" wrote: On Feb 14, 6:46 pm, Mike wrote: Not sure I've understood your question but the formula below returns the value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. THNK YOU MIKE- I will try as u said. What i need is to take the value in one reference cell and look in col-B and get the corresponding value in col-A. onlything is the numbers in col-B are in desending order. Further I also need to take next lower value from col-B and get value from col-A. Then interpolate to get correct value from the two values gotten from col-A for the refrence cell value. Actual scenario is I have tank soundings in col -A; corresponding quantity in col_B. So sometimes i need to see how much is the sounding by using the quantiy which will be in one refrence cell after calculation of required trim. Since the reference value may not be exactly the same as in col-B, i hv to take the two values in col-B and get two values of sounding from col_A and then interpolate for the reference cell. Sorry if i am unable to explain very clearly. Pls advice and once again thnks for ur quick response. YES MIKE I tried as u hv sugested. The problem is it gives #NA if the value looked in col_B is not exact. In my calculations the col_B figures will vary and will not be as in col_B. YESSS MARTIN, placing the -1 in the 'match' worked and thanks. Will you be kind enuff to also tell me if i have to take the next value from the col-B below the referenced value-- is there any way like offset method we use in vb which i can use in excel sheet as formula?? For col_A, I know it increases at regular interval but, for col_B it will be decreasing not steadily. Ahhh by the by GNVR is GADADOSS NAGABUSHANAM VENKAT RAJARAM--lol AND IT IS MY NAME . Thnks Martin and making progress. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
On Feb 14, 9:37 pm, bj wrote:
for what I think you want Interpolation of the values in column A corresponding to the values in column C above and below the Value in C1 try =INDEX(A:A,MATCH(C1,B:B,-1)) -(INDEX(A:A,MATCH(C1,B:B,-1))-(INDEX(A:A,MATCH(C1,B:B,-1)+1))*(INDEX(B:B,MATCH(C1,B:B,-1))-C1)/((INDEX(B:B,MATCH(C1,B:B,-1))-INDEX(B:B,MATCH(C1,B:B,-1)+1)) (hopefully all of parenthesis are corrent) "CAPTGNVR" wrote: On Feb 14, 8:29 pm, Martin Fishlock wrote: Hi Capt Gnvr: In the match formula there is an option switch on the end Mike used 0 exact match you can try using -1 or 1. Look at te help pages for match for the exact details of the match. BTW what is Gnvr? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 7:15 pm, "CAPTGNVR" wrote: On Feb 14, 6:46 pm, Mike wrote: Not sure I've understood your question but the formula below returns the value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. THNK YOU MIKE- I will try as u said. What i need is to take the value in one reference cell and look in col-B and get the corresponding value in col-A. onlything is the numbers in col-B are in desending order. Further I also need to take next lower value from col-B and get value from col-A. Then interpolate to get correct value from the two values gotten from col-A for the refrence cell value. Actual scenario is I have tank soundings in col -A; corresponding quantity in col_B. So sometimes i need to see how much is the sounding by using the quantiy which will be in one refrence cell after calculation of required trim. Since the reference value may not be exactly the same as in col-B, i hv to take the two values in col-B and get two values of sounding from col_A and then interpolate for the reference cell. Sorry if i am unable to explain very clearly. Pls advice and once again thnks for ur quick response. YES MIKE I tried as u hv sugested. The problem is it gives #NA if the value looked in col_B is not exact. In my calculations the col_B figures will vary and will not be as in col_B. YESSS MARTIN, placing the -1 in the 'match' worked and thanks. Will you be kind enuff to also tell me if i have to take the next value from the col-B below the referenced value-- is there any way like offset method we use in vb which i can use in excel sheet as formula?? For col_A, I know it increases at regular interval but, for col_B it will be decreasing not steadily. Ahhh by the by GNVR is GADADOSS NAGABUSHANAM VENKAT RAJARAM--lol AND IT IS MY NAME . Thnks Martin and making progress. D/BJ, u got what i was planning to do but like mr Martin has said one small example will clear. But I got the hang of how to go about it and was totally not aware that i could use both index and match in one formula. Very touched for the iinterpolation formula and i am modifying to suit my needs with the idea u hv given--thnks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
On Feb 14, 9:46 pm, Martin Fishlock
wrote: Hi Gadadoss, (if that is correct) There is the offset formula in vba or the indirect (which is like cells). The offset is like the OR or AND functions and you do =offset(cell,r,c....) I am not exactly sure what you are looking for so maybe an example would help. Please explain a little more on your requirements. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 8:29 pm, Martin Fishlock wrote: Hi Capt Gnvr: In the match formula there is an option switch on the end Mike used 0 exact match you can try using -1 or 1. Look at te help pages for match for the exact details of the match. BTW what is Gnvr? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 7:15 pm, "CAPTGNVR" wrote: On Feb 14, 6:46 pm, Mike wrote: Not sure I've understood your question but the formula below returns the value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. THNK YOU MIKE- I will try as u said. What i need is to take the value in one reference cell and look in col-B and get the corresponding value in col-A. onlything is the numbers in col-B are in desending order. Further I also need to take next lower value from col-B and get value from col-A. Then interpolate to get correct value from the two values gotten from col-A for the refrence cell value. Actual scenario is I have tank soundings in col -A; corresponding quantity in col_B. So sometimes i need to see how much is the sounding by using the quantiy which will be in one refrence cell after calculation of required trim. Since the reference value may not be exactly the same as in col-B, i hv to take the two values in col-B and get two values of sounding from col_A and then interpolate for the reference cell. Sorry if i am unable to explain very clearly. Pls advice and once again thnks for ur quick response. YES MIKE I tried as u hv sugested. The problem is it gives #NA if the value looked in col_B is not exact. In my calculations the col_B figures will vary and will not be as in col_B. YESSS MARTIN, placing the -1 in the 'match' worked and thanks. Will you be kind enuff to also tell me if i have to take the next value from the col-B below the referenced value-- is there any way like offset method we use in vb which i can use in excel sheet as formula?? For col_A, I know it increases at regular interval but, for col_B it will be decreasing not steadily. Ahhh by the by GNVR is GADADOSS NAGABUSHANAM VENKAT RAJARAM--lol AND IT IS MY NAME . Thnks Martin and making progress. D/MARTIN Gadadoss is fine. Thanks to the gprs internet on this ship, i am getting quick solutions and find it so encouraging to program. As u said I will send a sample of my sheet which I am programming. It is to find the tank soundings and/or tank quantities. How to send a small attachment -- can i send it to ur email?? brgds/CAPT VENKAT RAJARAM |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER
You can send it to martin_fishlock @ yahoo.co.uk.cutthis removing the spaces
and the .cutthis -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 9:46 pm, Martin Fishlock wrote: Hi Gadadoss, (if that is correct) There is the offset formula in vba or the indirect (which is like cells). The offset is like the OR or AND functions and you do =offset(cell,r,c....) I am not exactly sure what you are looking for so maybe an example would help. Please explain a little more on your requirements. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 8:29 pm, Martin Fishlock wrote: Hi Capt Gnvr: In the match formula there is an option switch on the end Mike used 0 exact match you can try using -1 or 1. Look at te help pages for match for the exact details of the match. BTW what is Gnvr? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CAPTGNVR" wrote: On Feb 14, 7:15 pm, "CAPTGNVR" wrote: On Feb 14, 6:46 pm, Mike wrote: Not sure I've understood your question but the formula below returns the value from column A from a match to the value in C1 found in column B =INDEX(A:A,MATCH(C1,B:B,0)) "CAPTGNVR" wrote: DEAR ALL I have say about 12 columns and the col-A is in ascending order. Using vlookup no problems to find out other values with ref to col-A values. What I require is to check from col-B which is in desending order and get value in the left of col-b that is col-A.. I Match and Index does not come handy bcos the numbers in col-B does not decrease proportionately so that I can a reference by row number. I have been managing by putting extra colum with the contents of the col- B sorted out in asending order and reverse the col-A in to another column say 13th column or so. Any better way addressing this pls adv. THNK YOU MIKE- I will try as u said. What i need is to take the value in one reference cell and look in col-B and get the corresponding value in col-A. onlything is the numbers in col-B are in desending order. Further I also need to take next lower value from col-B and get value from col-A. Then interpolate to get correct value from the two values gotten from col-A for the refrence cell value. Actual scenario is I have tank soundings in col -A; corresponding quantity in col_B. So sometimes i need to see how much is the sounding by using the quantiy which will be in one refrence cell after calculation of required trim. Since the reference value may not be exactly the same as in col-B, i hv to take the two values in col-B and get two values of sounding from col_A and then interpolate for the reference cell. Sorry if i am unable to explain very clearly. Pls advice and once again thnks for ur quick response. YES MIKE I tried as u hv sugested. The problem is it gives #NA if the value looked in col_B is not exact. In my calculations the col_B figures will vary and will not be as in col_B. YESSS MARTIN, placing the -1 in the 'match' worked and thanks. Will you be kind enuff to also tell me if i have to take the next value from the col-B below the referenced value-- is there any way like offset method we use in vb which i can use in excel sheet as formula?? For col_A, I know it increases at regular interval but, for col_B it will be decreasing not steadily. Ahhh by the by GNVR is GADADOSS NAGABUSHANAM VENKAT RAJARAM--lol AND IT IS MY NAME . Thnks Martin and making progress. D/MARTIN Gadadoss is fine. Thanks to the gprs internet on this ship, i am getting quick solutions and find it so encouraging to program. As u said I will send a sample of my sheet which I am programming. It is to find the tank soundings and/or tank quantities. How to send a small attachment -- can i send it to ur email?? brgds/CAPT VENKAT RAJARAM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use 2nd digit from the left in cell contents for vlookup | Excel Worksheet Functions | |||
How do I find the cell reference for a vlookup? | Excel Worksheet Functions | |||
Find text within cell then display text to left | Excel Discussion (Misc queries) | |||
Find LARGE, and th cell 3 cells to the left? | Excel Worksheet Functions | |||
Can Vlookup check a cell to the left? | Excel Worksheet Functions |