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