Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 115
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 115
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 115
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 694
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 115
Default 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   Report Post  
Posted to microsoft.public.excel.misc
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 694
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 115
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 115
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 694
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use 2nd digit from the left in cell contents for vlookup Pierre Excel Worksheet Functions 4 December 5th 06 07:40 PM
How do I find the cell reference for a vlookup? Curious Excel User Excel Worksheet Functions 2 August 2nd 06 11:50 PM
Find text within cell then display text to left Jambruins Excel Discussion (Misc queries) 5 April 17th 06 10:01 PM
Find LARGE, and th cell 3 cells to the left? Intotao Excel Worksheet Functions 1 January 9th 06 10:33 PM
Can Vlookup check a cell to the left? koala Excel Worksheet Functions 3 July 13th 05 02:31 AM


All times are GMT +1. The time now is 12:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"