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.

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.

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.

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.

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.

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.