can vlookup look up the result of a function?
 

=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.

can vlookup look up the result of a function?
 

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.

can vlookup look up the result of a function?
 

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.

can vlookup look up the result of a function?
 

"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

can vlookup look up the result of a function?
 

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.

can vlookup look up the result of a function?
 

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

can vlookup look up the result of a function?
 

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.

can vlookup look up the result of a function?
 

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

can vlookup look up the result of a function?
 

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

can vlookup look up the result of a function?
 

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.

can vlookup look up the result of a function?
 

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

can vlookup look up the result of a function?
 

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.

can vlookup look up the result of a function?
 

Yes RagDyer the horse is dead. I am obviously not the excel genius you are
and didn't think of the match thing. I have learned everything I know by
reading the help file and have created some pretty "*hot*" spreadsheets. I
am not that great with array formula's either but managed to modify JMB's
formula to my spreadsheet and make it work. If your the genius you seem to
thing you are then modify this to return a date the rate is available after
the first time it appears.

INDEX($V$3:$V$64,MATCH(FALSE,$U$3:$U$64<(J3+0.1),0 )) <- ctrl+shift+enter

Say... jan 15 my fx rate is 1.3456 and feb 23 the rate hits 1.4457. this
will be the date I need, but jan 2 the rate was 1.4456. The formula returns
jan 2 shich would no longer be an option.

thanx and remeber the horse is dead an no longer able to listen.

fryguy

"RagDyer" wrote:

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.

can vlookup look up the result of a function?
 

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.

can vlookup look up the result of a function?
 

Do I understand your problem?

J3 contains your original exchange rate.

U3 to U64 contains daily exchange rates.
V3 to V64 contains the date these rates are available.

You want to know the date that the original exchange rate (in J3) reaches a
value 0.1 higher.

Try this in a cell that's formatted to a date, so that you don't return the
date serial number:

=INDEX(V3:V64,MATCH(ROUND(J3+0.1,4),U3:U64,0))

This formula is *not* an array formula, just a regular <Enter will suffice.

NOW, you could also reference a cell in your formula which contains the
*rate increase* your looking to find.
That way you could play "what if", to see what dates the rate increased by
varying amounts.

Say J4 contained the amount of increase you're looking for.

Simply revise your formula to this:

=INDEX(V3:V64,MATCH(ROUND(J3+J4,4),U3:U64,0))

Now, is there anything that I perhaps misunderstood about your question?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
Yes RagDyer the horse is dead. I am obviously not the excel genius you
are
and didn't think of the match thing. I have learned everything I know by
reading the help file and have created some pretty "*hot*" spreadsheets.
I
am not that great with array formula's either but managed to modify JMB's
formula to my spreadsheet and make it work. If your the genius you seem
to
thing you are then modify this to return a date the rate is available
after
the first time it appears.

INDEX($V$3:$V$64,MATCH(FALSE,$U$3:$U$64<(J3+0.1),0 )) <-
ctrl+shift+enter

Say... jan 15 my fx rate is 1.3456 and feb 23 the rate hits 1.4457. this
will be the date I need, but jan 2 the rate was 1.4456. The formula
returns
jan 2 shich would no longer be an option.

thanx and remeber the horse is dead an no longer able to listen.

fryguy

"RagDyer" wrote:

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.

can vlookup look up the result of a function?
 

Yes unfortunatley you did miss siomething. The formula I provided returns
the date the rate hits equal or higher than J3, not an exact match. Although
you formula is simple enough with the refernece to the cell with the increase
it's not it.

The formula I submitted works better than the match formula but *can* return
a date that has already passed. I want to look back three months find out
that money that I purchased in June, if deposited now will score me a ten
point higher profit.

fryguy.

"Ragdyer" wrote:

Do I understand your problem?

J3 contains your original exchange rate.

U3 to U64 contains daily exchange rates.
V3 to V64 contains the date these rates are available.

You want to know the date that the original exchange rate (in J3) reaches a
value 0.1 higher.

Try this in a cell that's formatted to a date, so that you don't return the
date serial number:

=INDEX(V3:V64,MATCH(ROUND(J3+0.1,4),U3:U64,0))

This formula is *not* an array formula, just a regular <Enter will suffice.

NOW, you could also reference a cell in your formula which contains the
*rate increase* your looking to find.
That way you could play "what if", to see what dates the rate increased by
varying amounts.

Say J4 contained the amount of increase you're looking for.

Simply revise your formula to this:

=INDEX(V3:V64,MATCH(ROUND(J3+J4,4),U3:U64,0))

Now, is there anything that I perhaps misunderstood about your question?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
Yes RagDyer the horse is dead. I am obviously not the excel genius you
are
and didn't think of the match thing. I have learned everything I know by
reading the help file and have created some pretty "*hot*" spreadsheets.
I
am not that great with array formula's either but managed to modify JMB's
formula to my spreadsheet and make it work. If your the genius you seem
to
thing you are then modify this to return a date the rate is available
after
the first time it appears.

INDEX($V$3:$V$64,MATCH(FALSE,$U$3:$U$64<(J3+0.1),0 )) <-
ctrl+shift+enter

Say... jan 15 my fx rate is 1.3456 and feb 23 the rate hits 1.4457. this
will be the date I need, but jan 2 the rate was 1.4456. The formula
returns
jan 2 shich would no longer be an option.

thanx and remeber the horse is dead an no longer able to listen.

fryguy

"RagDyer" wrote:

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.

can vlookup look up the result of a function?
 

Don't understand this:

<<<"returns the date the rate hits equal or higher than J3, not an exact
match."

What's the difference between "equal" and "exact match"?

If it's "equal" to J3, then it's an "exact match" to J3, ... no?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
Yes unfortunatley you did miss siomething. The formula I provided returns
the date the rate hits equal or higher than J3, not an exact match.
Although
you formula is simple enough with the refernece to the cell with the
increase
it's not it.

The formula I submitted works better than the match formula but *can*
return
a date that has already passed. I want to look back three months find out
that money that I purchased in June, if deposited now will score me a ten
point higher profit.

fryguy.

"Ragdyer" wrote:

Do I understand your problem?

J3 contains your original exchange rate.

U3 to U64 contains daily exchange rates.
V3 to V64 contains the date these rates are available.

You want to know the date that the original exchange rate (in J3)
reaches a
value 0.1 higher.

Try this in a cell that's formatted to a date, so that you don't return
the
date serial number:

=INDEX(V3:V64,MATCH(ROUND(J3+0.1,4),U3:U64,0))

This formula is *not* an array formula, just a regular <Enter will
suffice.

NOW, you could also reference a cell in your formula which contains the
*rate increase* your looking to find.
That way you could play "what if", to see what dates the rate increased
by
varying amounts.

Say J4 contained the amount of increase you're looking for.

Simply revise your formula to this:

=INDEX(V3:V64,MATCH(ROUND(J3+J4,4),U3:U64,0))

Now, is there anything that I perhaps misunderstood about your question?
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"fryguy" wrote in message
...
Yes RagDyer the horse is dead. I am obviously not the excel genius
you
are
and didn't think of the match thing. I have learned everything I know
by
reading the help file and have created some pretty "*hot*"
spreadsheets.
I
am not that great with array formula's either but managed to modify
JMB's
formula to my spreadsheet and make it work. If your the genius you
seem
to
thing you are then modify this to return a date the rate is available
after
the first time it appears.

INDEX($V$3:$V$64,MATCH(FALSE,$U$3:$U$64<(J3+0.1),0 )) <-
ctrl+shift+enter

Say... jan 15 my fx rate is 1.3456 and feb 23 the rate hits 1.4457.
this
will be the date I need, but jan 2 the rate was 1.4456. The formula
returns
jan 2 shich would no longer be an option.

thanx and remeber the horse is dead an no longer able to listen.

fryguy

"RagDyer" wrote:

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.

can vlookup look up the result of a function?
 

once again the horse... dead.

thanx anyway rd

fg

"Ragdyer" wrote:

Don't understand this:

<<<"returns the date the rate hits equal or higher than J3, not an exact
match."

What's the difference between "equal" and "exact match"?

If it's "equal" to J3, then it's an "exact match" to J3, ... no?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fryguy" wrote in message
...
Yes unfortunatley you did miss siomething. The formula I provided returns
the date the rate hits equal or higher than J3, not an exact match.
Although
you formula is simple enough with the refernece to the cell with the
increase
it's not it.

The formula I submitted works better than the match formula but *can*
return
a date that has already passed. I want to look back three months find out
that money that I purchased in June, if deposited now will score me a ten
point higher profit.

fryguy.

"Ragdyer" wrote:

Do I understand your problem?

J3 contains your original exchange rate.

U3 to U64 contains daily exchange rates.
V3 to V64 contains the date these rates are available.

You want to know the date that the original exchange rate (in J3)
reaches a
value 0.1 higher.

Try this in a cell that's formatted to a date, so that you don't return
the
date serial number:

=INDEX(V3:V64,MATCH(ROUND(J3+0.1,4),U3:U64,0))

This formula is *not* an array formula, just a regular <Enter will
suffice.

NOW, you could also reference a cell in your formula which contains the
*rate increase* your looking to find.
That way you could play "what if", to see what dates the rate increased
by
varying amounts.

Say J4 contained the amount of increase you're looking for.

Simply revise your formula to this:

=INDEX(V3:V64,MATCH(ROUND(J3+J4,4),U3:U64,0))

Now, is there anything that I perhaps misunderstood about your question?
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"fryguy" wrote in message
...
Yes RagDyer the horse is dead. I am obviously not the excel genius
you
are
and didn't think of the match thing. I have learned everything I know
by
reading the help file and have created some pretty "*hot*"
spreadsheets.
I
am not that great with array formula's either but managed to modify
JMB's
formula to my spreadsheet and make it work. If your the genius you
seem
to
thing you are then modify this to return a date the rate is available
after
the first time it appears.

INDEX($V$3:$V$64,MATCH(FALSE,$U$3:$U$64<(J3+0.1),0 )) <-
ctrl+shift+enter

Say... jan 15 my fx rate is 1.3456 and feb 23 the rate hits 1.4457.
this
will be the date I need, but jan 2 the rate was 1.4456. The formula
returns
jan 2 shich would no longer be an option.

thanx and remeber the horse is dead an no longer able to listen.

fryguy

"RagDyer" wrote:

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.

can vlookup look up the result of a function?
 

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.

can vlookup look up the result of a function?
 

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.