Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with lookup reference please
Thank you again, Pete.
Jen B |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you lookup a value & get the cell reference? | Excel Worksheet Functions | |||
two way lookup and cell reference | Excel Worksheet Functions | |||
Multiple Reference Lookup | Excel Worksheet Functions | |||
Lookup and Reference | Excel Worksheet Functions | |||
lookup reference | Excel Worksheet Functions |