help with lookup reference please

[Jennifer B]

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.

 

[Spiky]

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)

 

[Spiky]

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.

 

[Pete_UK]

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

 

[Pete_UK]

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

 

[Jennifer B]

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

Jen B

 

[Pete_UK]

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

 

[Jennifer B]

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 havepartial 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,D2349))

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

 

[Pete_UK]

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:B49>0),D2349))

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]

Thank you again, Pete.

Jen B

 

[Pete_UK]

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

Pete