help with lookup reference please
 

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.

help with lookup reference please
 

On Jun 24, 1: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.

Assuming Sheet1 is the sheet TABLE1 is in and it starts in column A.
Notice that I think your 3rd argument is just supposed to be the
column number, I don't think names/titles work in VLOOKUP.

=VLOOKUP(INDEX(Sheet1!A2:A4,MATCH(A1,Sheet1!A2:A4, 1)),TABLE1,2,TRUE)

help with lookup reference please
 

Shoot. Forgot that MATCH will only work if your dates in TABLE1 are in
ascending order, like in your brief example. If not, something else
would have to be done.

help with lookup reference please
 

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.

help with lookup reference please
 

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 -

help with lookup reference please
 

This works - and is so much easier than what I was trying to create. thanks
for your help.

Jen B

help with lookup reference please
 

You're welcome, Jennifer - thanks for feeding back. Sorry to have
misled you with the earlier suggestion - I should really try more of
these out before posting them !! <bg

Pete

On Jun 25, 12:57*am, Jennifer B
wrote:
This works - and is so much easier than what I was trying to create. *thanks
for your help.

Jen B

help with lookup reference please
 

ok, I'm trying to use it elsewhere in the same worksheet, and now it's
reading it backwards! and I copied it and only changed the ranges....

here's what I have:(partial of the table)
Col B Col C Col D
Jan-08 0.00 0.00
Feb-08 80.00 80.00
Mar-08 (34.00) 46.00
46.00
46.00
46.00

Reference:
cell: $C$3
January-08

Formula:
=MAX(IF(B23:B49<=$C$3,D23:D49))

Results:
46.00

which is backwards! it should be 0.00

what did I do wrong? or is this flubbing up somewhere?

Jen B (very frustrated!)

help with lookup reference please
 

Those blank cells are less than Jan-08 (in Excel's view, anyway). So, you
need to amend the formula to:

=MAX(IF((B23:B49<=$C$3)*(B23:B490),D23:D49))

Don't forget to use CSE to commit the formula. This will not count cells
where the date is missing.

Hope this helps.

Pete

"Jennifer B" wrote in message
...

ok, I'm trying to use it elsewhere in the same worksheet, and now it's
reading it backwards! and I copied it and only changed the ranges....

here's what I have:(partial of the table)
Col B Col C Col D
Jan-08 0.00 0.00
Feb-08 80.00 80.00
Mar-08 (34.00) 46.00
46.00
46.00
46.00

Reference:
cell: $C$3
January-08

Formula:
=MAX(IF(B23:B49<=$C$3,D23:D49))

Results:
46.00

which is backwards! it should be 0.00

what did I do wrong? or is this flubbing up somewhere?

Jen B (very frustrated!)

help with lookup reference please
 

Thank you again, Pete.

Jen B

help with lookup reference please
 

That's okay, Jennifer. Thanks for taking the trouble to feed back.

Pete

On Jun 25, 5:04*pm, Jennifer B
wrote:
Thank you again, Pete.

Jen B