Sorry, scrub the SP formula - that doesn't work.

However, this array* formula does:

=MAX(IF(col1<=K54,col2))

where col1 and col2 are named ranges.

* An array formula has to be committed using CTRL-SHIFT-ENTER (CSE),
rather than the usual ENTER. When you do this correctly Excel wraps
curly braces { } around the formula when viewed in the formula bar -
do not type these yourself. Use CSE again if you edit the formula.

Hope this helps.

Pete


On Jun 24, 11:57*pm, Pete_UK wrote:
Try something like this:

=MAX(SUMPRODUCT((COL1<=K54)*(COL2)))

I'm assuming that COL1 and COL2 etc are named ranges for the data
below.

Hope this helps.

Pete

On Jun 24, 7:51*pm, Jennifer B
wrote:



Hi,

*I have several worksheets that I am referencing between. *I have a
worksheet with a table (named TABLE1) set up like below:

"COL1" * * * * "COL2" * * * * *"COL3" * * * * * "COL4" * * * * * *"COL5"
Jan-08 * * <amount 1 * <amount 2 * <amount 3 * <amount 4
Mar-08 * *<amount 1 * <amount 2 * <amount 3 * <amount 4
June-08 * <amount 1 * <amount 2 * <amount 3 * <amount 4

on another worksheet, I have a cell with a date:

(A1) *July-08

in another cell on the same worksheet, (C6) I want to put a formula that
a) *looks through the dates in TABLE1,
b) *finds the dates that are less than or equal to A1
c) *chose the date that is the largest value to meet the criteria
d) *returns <amount 1 from that line

I know it will have a VLOOKUP in it, but it's the criteria that I'm having
trouble defining. * *This is what I have so far...

=VLOOKUP(K54,TABLE1,COL2,TRUE)

but it wouldn't necessarily show the largest value to meet the criteria, and
I get a #REF output. *There must be another formula to add into it to make it
work?

Thanks.- Hide quoted text -

- Show quoted text -