=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.

Have you tried it before posting here?

Manually key in some test values, and check it out.

It should work fine!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a
reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I
need
to lookup the same rate when it reaches 1.1735 and return the date it
reahces
this rate. It would be a two column lookup_array, rate and date.

Of course I tried it.

The help file specifically states "Lookup_value... Lookup_value can be a
value or a reference. If loo..." So it can't be the result of addition or a
sum function. It doesn't even work if you make a refernece to a cell with
the sum function, that returns the correct value of 1.1735.

Does anyone else have any other ideas!?


"RagDyer" wrote:

Have you tried it before posting here?

Manually key in some test values, and check it out.

It should work fine!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a
reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I
need
to lookup the same rate when it reaches 1.1735 and return the date it
reahces
this rate. It would be a two column lookup_array, rate and date.

The formula in your OP *WILL* work *exactly* as you say you want it to.

The reason *you* can't get it to do so, *is* the main question!

Probably, your data values are *not* as you may think they are.

If these values are being imported, there are numerous possibilities for
contamination, so that they are not recognized by XL as they may appear to
the eye.

That's the reason I suggested keying in values yourself, so that you're sure
that *both* the lookup values in the datalist and the cells to be calculated
are exactly the same.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
Of course I tried it.

The help file specifically states "Lookup_value... Lookup_value can be a
value or a reference. If loo..." So it can't be the result of addition or
a
sum function. It doesn't even work if you make a refernece to a cell with
the sum function, that returns the correct value of 1.1735.

Does anyone else have any other ideas!?


"RagDyer" wrote:

Have you tried it before posting here?

Manually key in some test values, and check it out.

It should work fine!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a
reference
plus an amount? I need to look up a foreign exchange rate when it
reaches
ten points higher. So... 1.0735 is the original exchange rate, and I
need
to lookup the same rate when it reaches 1.1735 and return the date it
reahces
this rate. It would be a two column lookup_array, rate and date.

See the suggestions by Pete_UK below, it worked. I am manually keying in all
the figures and I will be when this spreadsheet is finished. None of the
values have been imported from any other program.

Thanks anyway for your help.

fryguy

"RagDyer" wrote:

The formula in your OP *WILL* work *exactly* as you say you want it to.

The reason *you* can't get it to do so, *is* the main question!

Probably, your data values are *not* as you may think they are.

If these values are being imported, there are numerous possibilities for
contamination, so that they are not recognized by XL as they may appear to
the eye.

That's the reason I suggested keying in values yourself, so that you're sure
that *both* the lookup values in the datalist and the cells to be calculated
are exactly the same.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
Of course I tried it.

The help file specifically states "Lookup_value... Lookup_value can be a
value or a reference. If loo..." So it can't be the result of addition or
a
sum function. It doesn't even work if you make a refernece to a cell with
the sum function, that returns the correct value of 1.1735.

Does anyone else have any other ideas!?


"RagDyer" wrote:

Have you tried it before posting here?

Manually key in some test values, and check it out.

It should work fine!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a
reference
plus an amount? I need to look up a foreign exchange rate when it
reaches
ten points higher. So... 1.0735 is the original exchange rate, and I
need
to lookup the same rate when it reaches 1.1735 and return the date it
reahces
this rate. It would be a two column lookup_array, rate and date.

Not to kick a dead horse, but all that Pete's formula is doing is limiting
the calculation cell to 4 decimal places to match the 4 decimal places in
your lookup list!

Do you mean to tell me that when you manually entered test data to check out
the veracity of your original formula, you *didn't* think to exactly match
*both* numbers out to 4 places?

Don't you think that would be elementary in any testing ... !
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
See the suggestions by Pete_UK below, it worked. I am manually keying in
all
the figures and I will be when this spreadsheet is finished. None of the
values have been imported from any other program.

Thanks anyway for your help.

fryguy

"RagDyer" wrote:

The formula in your OP *WILL* work *exactly* as you say you want it to.

The reason *you* can't get it to do so, *is* the main question!

Probably, your data values are *not* as you may think they are.

If these values are being imported, there are numerous possibilities for
contamination, so that they are not recognized by XL as they may appear
to
the eye.

That's the reason I suggested keying in values yourself, so that you're
sure
that *both* the lookup values in the datalist and the cells to be
calculated
are exactly the same.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
Of course I tried it.

The help file specifically states "Lookup_value... Lookup_value can
be a
value or a reference. If loo..." So it can't be the result of addition
or
a
sum function. It doesn't even work if you make a refernece to a cell
with
the sum function, that returns the correct value of 1.1735.

Does anyone else have any other ideas!?


"RagDyer" wrote:

Have you tried it before posting here?

Manually key in some test values, and check it out.

It should work fine!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a
reference
plus an amount? I need to look up a foreign exchange rate when it
reaches
ten points higher. So... 1.0735 is the original exchange rate, and
I
need
to lookup the same rate when it reaches 1.1735 and return the date
it
reahces
this rate. It would be a two column lookup_array, rate and date.

"fryguy" wrote:

=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.

fryguy
Just a thought, Lookup will only work if the lookup reference is in
ascending order and rates, I assume fluctuate, so you need to sort the rates.
if the rate has not reached the amount you need to know that.

try something like
if(iserr(vlookup((a1+0.1),$B$1:$C$45,2,FALSE)),"No t
reached",vlookup((a1+0.1),$B$1:$C$45,2,FALSE))

I haven't tried it but it might be what you are after.

Peter

Peter you are incorrect about the sorting the rates thing. If the
lookup_range is set to FALSE then it will find the first match to the
criteria. TRUE requires a sorted list.

I found out what the problem is it will only work if a multiple of 0.5 is
added or subtracted with the lookup_value (.5,1.5,1,-2.5,etc.) thus 0.1 will
not.

If anyone can figure this out before I do please let me know :)

fryguy



"PeterAtherton" wrote:

"fryguy" wrote:

=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.

fryguy
Just a thought, Lookup will only work if the lookup reference is in
ascending order and rates, I assume fluctuate, so you need to sort the rates.
if the rate has not reached the amount you need to know that.

try something like
if(iserr(vlookup((a1+0.1),$B$1:$C$45,2,FALSE)),"No t
reached",vlookup((a1+0.1),$B$1:$C$45,2,FALSE))

I haven't tried it but it might be what you are after.

Peter

Try this:

=vlookup(ROUND(a1+0.1,4),$B$1:$C$45,2,FALSE)

which should match on 4 decimal places.

Hope this helps.

Pete

fryguy wrote:
Peter you are incorrect about the sorting the rates thing. If the
lookup_range is set to FALSE then it will find the first match to the
criteria. TRUE requires a sorted list.

I found out what the problem is it will only work if a multiple of 0.5 is
added or subtracted with the lookup_value (.5,1.5,1,-2.5,etc.) thus 0.1 will
not.

If anyone can figure this out before I do please let me know :)

fryguy



"PeterAtherton" wrote:

"fryguy" wrote:

=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.

fryguy
Just a thought, Lookup will only work if the lookup reference is in
ascending order and rates, I assume fluctuate, so you need to sort the rates.
if the rate has not reached the amount you need to know that.

try something like
if(iserr(vlookup((a1+0.1),$B$1:$C$45,2,FALSE)),"No t
reached",vlookup((a1+0.1),$B$1:$C$45,2,FALSE))

I haven't tried it but it might be what you are after.

Peter

This worked just fine!!!!! Thanks Pete_UK

fryguy

"Pete_UK" wrote:

Try this:

=vlookup(ROUND(a1+0.1,4),$B$1:$C$45,2,FALSE)

which should match on 4 decimal places.

Hope this helps.

Pete

fryguy wrote:
Peter you are incorrect about the sorting the rates thing. If the
lookup_range is set to FALSE then it will find the first match to the
criteria. TRUE requires a sorted list.

I found out what the problem is it will only work if a multiple of 0.5 is
added or subtracted with the lookup_value (.5,1.5,1,-2.5,etc.) thus 0.1 will
not.

If anyone can figure this out before I do please let me know :)

fryguy



"PeterAtherton" wrote:

"fryguy" wrote:

=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.

fryguy
Just a thought, Lookup will only work if the lookup reference is in
ascending order and rates, I assume fluctuate, so you need to sort the rates.
if the rate has not reached the amount you need to know that.

try something like
if(iserr(vlookup((a1+0.1),$B$1:$C$45,2,FALSE)),"No t
reached",vlookup((a1+0.1),$B$1:$C$45,2,FALSE))

I haven't tried it but it might be what you are after.

Peter

You're welcome.

Pete

fryguy wrote:
This worked just fine!!!!! Thanks Pete_UK

fryguy

"Pete_UK" wrote:

Try this:

=vlookup(ROUND(a1+0.1,4),$B$1:$C$45,2,FALSE)

which should match on 4 decimal places.

Hope this helps.

Pete

fryguy wrote:
Peter you are incorrect about the sorting the rates thing. If the
lookup_range is set to FALSE then it will find the first match to the
criteria. TRUE requires a sorted list.

I found out what the problem is it will only work if a multiple of 0.5 is
added or subtracted with the lookup_value (.5,1.5,1,-2.5,etc.) thus 0.1 will
not.

If anyone can figure this out before I do please let me know :)

fryguy



"PeterAtherton" wrote:

"fryguy" wrote:

=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.

fryguy
Just a thought, Lookup will only work if the lookup reference is in
ascending order and rates, I assume fluctuate, so you need to sort the rates.
if the rate has not reached the amount you need to know that.

try something like
if(iserr(vlookup((a1+0.1),$B$1:$C$45,2,FALSE)),"No t
reached",vlookup((a1+0.1),$B$1:$C$45,2,FALSE))

I haven't tried it but it might be what you are after.

Peter

VLookup can work with a reference plus amount. Are there any extra decimal
places in your data (either in the table or in cell A1)? Also, does 1.1735
appear (exactly) in your table or are you looking for the date the exchange
rate is equal to or greater than 1.1735? With the 4th argument set to FALSE,
you are trying to find an exact match.

How is your data sorted? By the exchange rate or date and is it ascending
or descending?

If you want the first date where the exchange rate is = A1+0.1, then this
should work regardless of how your data is organized:
=INDEX(C1:C45,MATCH(FALSE,B1:B45<(A1+0.1),0))

array entered with Control+Shift+Enter (otherwise you'll get #VALUE!).


"fryguy" wrote:

=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.

Hey JMB have a look at the reply to the last message from RagDyer and see
what you come up with.

thanx again your's has been the best so far.

fryguy.

"JMB" wrote:

VLookup can work with a reference plus amount. Are there any extra decimal
places in your data (either in the table or in cell A1)? Also, does 1.1735
appear (exactly) in your table or are you looking for the date the exchange
rate is equal to or greater than 1.1735? With the 4th argument set to FALSE,
you are trying to find an exact match.

How is your data sorted? By the exchange rate or date and is it ascending
or descending?

If you want the first date where the exchange rate is = A1+0.1, then this
should work regardless of how your data is organized:
=INDEX(C1:C45,MATCH(FALSE,B1:B45<(A1+0.1),0))

array entered with Control+Shift+Enter (otherwise you'll get #VALUE!).


"fryguy" wrote:

=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.

My understanding is that you want the last date within the past 3 months the
rate was = the current rate

Try:
=INDEX($V$3:$V$5,MATCH(1,($U$3:$U$64=(J3+0.1))*($ V$3:$V$5=EDATE(TODAY(),-3)),0))

array entered. Also, EDATE requires the analysis toolpak be installed and
enabled (if not, I imagine you will get a #NAME? error)

If that does not help, you should post some sample data and the expected
results.

"fryguy" wrote:

Hey JMB have a look at the reply to the last message from RagDyer and see
what you come up with.

thanx again your's has been the best so far.

fryguy.

"JMB" wrote:

VLookup can work with a reference plus amount. Are there any extra decimal
places in your data (either in the table or in cell A1)? Also, does 1.1735
appear (exactly) in your table or are you looking for the date the exchange
rate is equal to or greater than 1.1735? With the 4th argument set to FALSE,
you are trying to find an exact match.

How is your data sorted? By the exchange rate or date and is it ascending
or descending?

If you want the first date where the exchange rate is = A1+0.1, then this
should work regardless of how your data is organized:
=INDEX(C1:C45,MATCH(FALSE,B1:B45<(A1+0.1),0))

array entered with Control+Shift+Enter (otherwise you'll get #VALUE!).


"fryguy" wrote:

=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.

Small correction (I had a reference to U64). Also, I misspoke. This formula
will give you the oldest date in the past 3 months meeting the criteria I
mentioned (array entered):


=INDEX($V$3:$V$5,MATCH(1,($U$3:$U$5=(J3+0.1))*($V $3:$V$5=EDATE(TODAY(),-3)),0))

This I think should give you the most recent date within the past 3 months
meeting the criteria I specified (non-array formula):
=LOOKUP(2,1/(($U$3:$U$6=(J3+0.1))*($V$3:$V$6=EDATE(TODAY(),-3))),$V$3:$V$6)

"JMB" wrote:

My understanding is that you want the last date within the past 3 months the
rate was = the current rate

Try:
=INDEX($V$3:$V$5,MATCH(1,($U$3:$U$64=(J3+0.1))*($ V$3:$V$5=EDATE(TODAY(),-3)),0))

array entered. Also, EDATE requires the analysis toolpak be installed and
enabled (if not, I imagine you will get a #NAME? error)

If that does not help, you should post some sample data and the expected
results.

"fryguy" wrote:

Hey JMB have a look at the reply to the last message from RagDyer and see
what you come up with.

thanx again your's has been the best so far.

fryguy.

"JMB" wrote:

VLookup can work with a reference plus amount. Are there any extra decimal
places in your data (either in the table or in cell A1)? Also, does 1.1735
appear (exactly) in your table or are you looking for the date the exchange
rate is equal to or greater than 1.1735? With the 4th argument set to FALSE,
you are trying to find an exact match.

How is your data sorted? By the exchange rate or date and is it ascending
or descending?

If you want the first date where the exchange rate is = A1+0.1, then this
should work regardless of how your data is organized:
=INDEX(C1:C45,MATCH(FALSE,B1:B45<(A1+0.1),0))

array entered with Control+Shift+Enter (otherwise you'll get #VALUE!).


"fryguy" wrote:

=vlookup((a1+0.1),$B$1:$C$45,2,FALSE) Can the lookup_value be a reference
plus an amount? I need to look up a foreign exchange rate when it reaches
ten points higher. So... 1.0735 is the original exchange rate, and I need
to lookup the same rate when it reaches 1.1735 and return the date it reahces
this rate. It would be a two column lookup_array, rate and date.