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.