Re: can vlookup look up the result of a function?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <fryguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D3CBE4FD-3810-44C7-B318-66E3CD2492C1@xxxxxxxxxxxxxxxx
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" <fryguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DD09C6F3-3AFE-49AF-BCB6-950BDE2F73EF@xxxxxxxxxxxxxxxx
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" <fryguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:47CB1F0C-67A2-47C6-B5EE-5B5ABAF4F076@xxxxxxxxxxxxxxxx
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" <fryguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7E74ABE4-3C50-4124-BB46-C946578012F5@xxxxxxxxxxxxxxxx
=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.







.

Relevant Pages

  • Re: can vlookup look up the result of a function?
    ... the date the rate hits equal or higher than J3, not an exact match. ... "Ragdyer" wrote: ... J3 contains your original exchange rate. ... Try this in a cell that's formatted to a date, so that you don't return the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: can vlookup look up the result of a function?
    ... "Ragdyer" wrote: ... You want to know the date that the original exchange rate ... Try this in a cell that's formatted to a date, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How can wizards be so TOTALLY stupid about muggle stuff?
    ... An exchange rate is an exchange rate, it doesn't matter which direction it ... has to be a fairly large amount of cross-pollination in that regard. ... in order to get the large amount of food they would need. ... The matter of food creation isn't as clearcut as you put it... ...
    (alt.fan.harry-potter)
  • Re: can vlookup look up the result of a function?
    ... "Ragdyer" wrote: ... You want to know the date that the original exchange rate ... thanx and remeber the horse is dead an no longer able to listen. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Converting dollars into euros
    ... Just multiply the amount by the exchange rate. ... SO if the amount is in A1, and the exchange rate is in C3 on Sheet2, use ... dollars and see the amount in euros in the same cell. ...
    (microsoft.public.excel)