Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX w. MATCH function
am attempting to use the index and match functions to find the intersection
of project names and specific dates. The project names are along the rows in the lefthand side and the dates are in one row across several columns. I have set my index area, been able to match the project name using quotations, but the formula will not let me return a column for the dates, I have tried formattion the date, using quotations, but nothing will work. Can the MATCH function be used with dates? Is there another formula to accomplish this? The setup is as follows and as well as my formula, as always any help is most appreciated 1/1/2009 2/2/2009 3/3/2009 4/4/2009 Project A Project B Project C =INDEX(schedule,MATCH("Project A",project list,0),MATCH("1/1/2009",dates,0)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX w. MATCH function
You might want to try a VLOOKUP with match instead:
=VLOOKUP(project name,$A$1:$M$13,MATCH(date,$A$1:$M$1,0),0) This assumes your table with column and row headers is in cells A1:M13 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: am attempting to use the index and match functions to find the intersection of project names and specific dates. The project names are along the rows in the lefthand side and the dates are in one row across several columns. I have set my index area, been able to match the project name using quotations, but the formula will not let me return a column for the dates, I have tried formattion the date, using quotations, but nothing will work. Can the MATCH function be used with dates? Is there another formula to accomplish this? The setup is as follows and as well as my formula, as always any help is most appreciated 1/1/2009 2/2/2009 3/3/2009 4/4/2009 Project A Project B Project C =INDEX(schedule,MATCH("Project A",project list,0),MATCH("1/1/2009",dates,0)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX w. MATCH function
Try using cells to hold the lookup criteria:
A1 = Project A B1 = 1/1/2009 =INDEX(schedule,MATCH(A1,projectlist,0),MATCH(B1,d ates,0)) -- Biff Microsoft Excel MVP "dannyboy8" wrote in message ... am attempting to use the index and match functions to find the intersection of project names and specific dates. The project names are along the rows in the lefthand side and the dates are in one row across several columns. I have set my index area, been able to match the project name using quotations, but the formula will not let me return a column for the dates, I have tried formattion the date, using quotations, but nothing will work. Can the MATCH function be used with dates? Is there another formula to accomplish this? The setup is as follows and as well as my formula, as always any help is most appreciated 1/1/2009 2/2/2009 3/3/2009 4/4/2009 Project A Project B Project C =INDEX(schedule,MATCH("Project A",project list,0),MATCH("1/1/2009",dates,0)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX w. MATCH function
John, still getting a n/a, what is incorrect w/ my formula below?
=VLOOKUP("project A",schedule,MATCH("1/1/2009",dates,0),0) also, the names I have given to schedule and dates have ranges of A7:AE79 and A7:AE7 respectively, still no luck "John C" wrote: You might want to try a VLOOKUP with match instead: =VLOOKUP(project name,$A$1:$M$13,MATCH(date,$A$1:$M$1,0),0) This assumes your table with column and row headers is in cells A1:M13 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: am attempting to use the index and match functions to find the intersection of project names and specific dates. The project names are along the rows in the lefthand side and the dates are in one row across several columns. I have set my index area, been able to match the project name using quotations, but the formula will not let me return a column for the dates, I have tried formattion the date, using quotations, but nothing will work. Can the MATCH function be used with dates? Is there another formula to accomplish this? The setup is as follows and as well as my formula, as always any help is most appreciated 1/1/2009 2/2/2009 3/3/2009 4/4/2009 Project A Project B Project C =INDEX(schedule,MATCH("Project A",project list,0),MATCH("1/1/2009",dates,0)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX w. MATCH function
Chances are good that your dates aren't really 'dates', either in your row 7
or in your actual vlookup. Most likely the trouble is with row 7, your dates range. You can test this by typing the following in a blank cell above/below your row 7. Assuming your dates actually start in B7 =ISNUMBER(B7), and copy across to column AE. How are the dates in row 7 computed? I only ask because most people that go from month A to month B don't go from 1/1/2009 to 2/2/2009 to 3/3/2009 etc. They usually make everything one specific day of the month. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: John, still getting a n/a, what is incorrect w/ my formula below? =VLOOKUP("project A",schedule,MATCH("1/1/2009",dates,0),0) also, the names I have given to schedule and dates have ranges of A7:AE79 and A7:AE7 respectively, still no luck "John C" wrote: You might want to try a VLOOKUP with match instead: =VLOOKUP(project name,$A$1:$M$13,MATCH(date,$A$1:$M$1,0),0) This assumes your table with column and row headers is in cells A1:M13 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: am attempting to use the index and match functions to find the intersection of project names and specific dates. The project names are along the rows in the lefthand side and the dates are in one row across several columns. I have set my index area, been able to match the project name using quotations, but the formula will not let me return a column for the dates, I have tried formattion the date, using quotations, but nothing will work. Can the MATCH function be used with dates? Is there another formula to accomplish this? The setup is as follows and as well as my formula, as always any help is most appreciated 1/1/2009 2/2/2009 3/3/2009 4/4/2009 Project A Project B Project C =INDEX(schedule,MATCH("Project A",project list,0),MATCH("1/1/2009",dates,0)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX w. MATCH function
I'd try:
=VLOOKUP("project A",schedule,MATCH(date(2009,1,1),dates,0),0) dannyboy8 wrote: John, still getting a n/a, what is incorrect w/ my formula below? =VLOOKUP("project A",schedule,MATCH("1/1/2009",dates,0),0) also, the names I have given to schedule and dates have ranges of A7:AE79 and A7:AE7 respectively, still no luck "John C" wrote: You might want to try a VLOOKUP with match instead: =VLOOKUP(project name,$A$1:$M$13,MATCH(date,$A$1:$M$1,0),0) This assumes your table with column and row headers is in cells A1:M13 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: am attempting to use the index and match functions to find the intersection of project names and specific dates. The project names are along the rows in the lefthand side and the dates are in one row across several columns. I have set my index area, been able to match the project name using quotations, but the formula will not let me return a column for the dates, I have tried formattion the date, using quotations, but nothing will work. Can the MATCH function be used with dates? Is there another formula to accomplish this? The setup is as follows and as well as my formula, as always any help is most appreciated 1/1/2009 2/2/2009 3/3/2009 4/4/2009 Project A Project B Project C =INDEX(schedule,MATCH("Project A",project list,0),MATCH("1/1/2009",dates,0)) -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX w. MATCH function
John, I tested the isnumber function across and it came up true for all of
the "dates". AS for how the dates are computed, they are not really in any true order as the dates are formatted only to show the Jan-09 diction in the cell, eventhough when positioned in the cell it will state 1/2/2009. Where am I getting tripped up on this? "John C" wrote: Chances are good that your dates aren't really 'dates', either in your row 7 or in your actual vlookup. Most likely the trouble is with row 7, your dates range. You can test this by typing the following in a blank cell above/below your row 7. Assuming your dates actually start in B7 =ISNUMBER(B7), and copy across to column AE. How are the dates in row 7 computed? I only ask because most people that go from month A to month B don't go from 1/1/2009 to 2/2/2009 to 3/3/2009 etc. They usually make everything one specific day of the month. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: John, still getting a n/a, what is incorrect w/ my formula below? =VLOOKUP("project A",schedule,MATCH("1/1/2009",dates,0),0) also, the names I have given to schedule and dates have ranges of A7:AE79 and A7:AE7 respectively, still no luck "John C" wrote: You might want to try a VLOOKUP with match instead: =VLOOKUP(project name,$A$1:$M$13,MATCH(date,$A$1:$M$1,0),0) This assumes your table with column and row headers is in cells A1:M13 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: am attempting to use the index and match functions to find the intersection of project names and specific dates. The project names are along the rows in the lefthand side and the dates are in one row across several columns. I have set my index area, been able to match the project name using quotations, but the formula will not let me return a column for the dates, I have tried formattion the date, using quotations, but nothing will work. Can the MATCH function be used with dates? Is there another formula to accomplish this? The setup is as follows and as well as my formula, as always any help is most appreciated 1/1/2009 2/2/2009 3/3/2009 4/4/2009 Project A Project B Project C =INDEX(schedule,MATCH("Project A",project list,0),MATCH("1/1/2009",dates,0)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX w. MATCH function
Well, if for example you have the Feb-09 diction, but in reality it is
2/2/2009, then if you are trying to match 2/1/2009, then it will not match. What you should do is have a basic day (i.e.: the 1st) for all dates in A7:AE7. You can format it to appear however you want it to appear, but the day must match your lookup value. From your own remark, the formula bar shows the date as 1/2/2009, but you are trying to find a match of "1/1/2009". They obviously do not match. I would change all the 'days' of A7:AE7 to 1.....1/1/2009 2/1/2009 3/1/2009 etc. Then when you match, you can either use "1/1/2009" or, I would maybe even recommend using the DATE function as Dave P points out: =VLOOKUP("project A",schedule,MATCH(DATE(2009,1,1),dates,0),0) Actually, I really recommend having 2 criteria cells in which to plug into your formula rather than hardcoding the VLOOKUP. Say your table is on another sheet (your two ranges dates and schedule Say the month you are looking for is in cell B1 and the date you are looking for is in cell A1 =VLOOKUP(A1,schedule,MATCH(B1,dates,0),0) Then you could look up anything just by changing your two parameter cells (or you could make more parameter cells below A1:B1 and just copy the formula down). Always remember to enter the date with a consistent entry of the day of the month for your table as well as your criteria. -- ** John C ** "dannyboy8" wrote: John, I tested the isnumber function across and it came up true for all of the "dates". AS for how the dates are computed, they are not really in any true order as the dates are formatted only to show the Jan-09 diction in the cell, eventhough when positioned in the cell it will state 1/2/2009. Where am I getting tripped up on this? "John C" wrote: Chances are good that your dates aren't really 'dates', either in your row 7 or in your actual vlookup. Most likely the trouble is with row 7, your dates range. You can test this by typing the following in a blank cell above/below your row 7. Assuming your dates actually start in B7 =ISNUMBER(B7), and copy across to column AE. How are the dates in row 7 computed? I only ask because most people that go from month A to month B don't go from 1/1/2009 to 2/2/2009 to 3/3/2009 etc. They usually make everything one specific day of the month. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: John, still getting a n/a, what is incorrect w/ my formula below? =VLOOKUP("project A",schedule,MATCH("1/1/2009",dates,0),0) also, the names I have given to schedule and dates have ranges of A7:AE79 and A7:AE7 respectively, still no luck "John C" wrote: You might want to try a VLOOKUP with match instead: =VLOOKUP(project name,$A$1:$M$13,MATCH(date,$A$1:$M$1,0),0) This assumes your table with column and row headers is in cells A1:M13 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: am attempting to use the index and match functions to find the intersection of project names and specific dates. The project names are along the rows in the lefthand side and the dates are in one row across several columns. I have set my index area, been able to match the project name using quotations, but the formula will not let me return a column for the dates, I have tried formattion the date, using quotations, but nothing will work. Can the MATCH function be used with dates? Is there another formula to accomplish this? The setup is as follows and as well as my formula, as always any help is most appreciated 1/1/2009 2/2/2009 3/3/2009 4/4/2009 Project A Project B Project C =INDEX(schedule,MATCH("Project A",project list,0),MATCH("1/1/2009",dates,0)) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX w. MATCH function
WE HAVE A WINNER! Thanks Dave, and to everyone else as well!
"Dave Peterson" wrote: I'd try: =VLOOKUP("project A",schedule,MATCH(date(2009,1,1),dates,0),0) dannyboy8 wrote: John, still getting a n/a, what is incorrect w/ my formula below? =VLOOKUP("project A",schedule,MATCH("1/1/2009",dates,0),0) also, the names I have given to schedule and dates have ranges of A7:AE79 and A7:AE7 respectively, still no luck "John C" wrote: You might want to try a VLOOKUP with match instead: =VLOOKUP(project name,$A$1:$M$13,MATCH(date,$A$1:$M$1,0),0) This assumes your table with column and row headers is in cells A1:M13 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "dannyboy8" wrote: am attempting to use the index and match functions to find the intersection of project names and specific dates. The project names are along the rows in the lefthand side and the dates are in one row across several columns. I have set my index area, been able to match the project name using quotations, but the formula will not let me return a column for the dates, I have tried formattion the date, using quotations, but nothing will work. Can the MATCH function be used with dates? Is there another formula to accomplish this? The setup is as follows and as well as my formula, as always any help is most appreciated 1/1/2009 2/2/2009 3/3/2009 4/4/2009 Project A Project B Project C =INDEX(schedule,MATCH("Project A",project list,0),MATCH("1/1/2009",dates,0)) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match and Index function | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
index match function help me | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Need help on index and match function | Excel Worksheet Functions |