can vlookup look up the result of a function?

F

fryguy

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

RagDyer

Have you tried it before posting here?

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

It should work fine!
 
F

fryguy

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!?
 
P

PeterAtherton

fryguy said:
=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.
Click to expand...

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)),"Not
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
 
J

JMB

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!).
 
F

fryguy

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
 
R

RagDyer

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

Pete_UK

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
 
F

fryguy

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
 
R

RagDyer

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 ... !
 
F

fryguy

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
 
F

fryguy

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

Ragdyer

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?
 
F

fryguy

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

Ragdyer

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?
 
J

JMB

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

JMB

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)
 
Top