| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining MATCH & LOOKUP formulas
I am trying to find a formula that matches the "Names" from my summary sheet
to a separate worksheet and if the names match, bring back the most recent date for that person. Summary sheet(requires formula): 09/10/2008 Name Yesterday's numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/08/2008 26 27 28 29 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining MATCH & LOOKUP formulas
"Dates" is the range of dates
"Names" is the range of names "Tbl" is all the other data under Names and to the right of Dates "Thisday" is the cell with today's date "Tgt" is the name to find =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) "Txlonghorn76" wrote: I am trying to find a formula that matches the "Names" from my summary sheet to a separate worksheet and if the names match, bring back the most recent date for that person. Summary sheet(requires formula): 09/10/2008 Name Yesterday's numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/08/2008 26 27 28 29 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining MATCH & LOOKUP formulas
I am getting a #NAME? error.
"Duke Carey" wrote: "Dates" is the range of dates "Names" is the range of names "Tbl" is all the other data under Names and to the right of Dates "Thisday" is the cell with today's date "Tgt" is the name to find =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) "Txlonghorn76" wrote: I am trying to find a formula that matches the "Names" from my summary sheet to a separate worksheet and if the names match, bring back the most recent date for that person. Summary sheet(requires formula): 09/10/2008 Name Yesterday's numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/08/2008 26 27 28 29 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining MATCH & LOOKUP formulas
Here is the formula:
=INDEX(Sheet1!B2:F9,MATCH(A1,Sheet1!B3:B9,0),MATCH ("AAA",Sheet1!C2:F2,0)) I am getting 09/09/2008 in the cell. "Txlonghorn76" wrote: I am getting a #NAME? error. "Duke Carey" wrote: "Dates" is the range of dates "Names" is the range of names "Tbl" is all the other data under Names and to the right of Dates "Thisday" is the cell with today's date "Tgt" is the name to find =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) "Txlonghorn76" wrote: I am trying to find a formula that matches the "Names" from my summary sheet to a separate worksheet and if the names match, bring back the most recent date for that person. Summary sheet(requires formula): 09/10/2008 Name Yesterday's numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/08/2008 26 27 28 29 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining MATCH & LOOKUP formulas
Duke,
It worked! Thanks! "Txlonghorn76" wrote: Here is the formula: =INDEX(Sheet1!B2:F9,MATCH(A1,Sheet1!B3:B9,0),MATCH ("AAA",Sheet1!C2:F2,0)) I am getting 09/09/2008 in the cell. "Txlonghorn76" wrote: I am getting a #NAME? error. "Duke Carey" wrote: "Dates" is the range of dates "Names" is the range of names "Tbl" is all the other data under Names and to the right of Dates "Thisday" is the cell with today's date "Tgt" is the name to find =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) "Txlonghorn76" wrote: I am trying to find a formula that matches the "Names" from my summary sheet to a separate worksheet and if the names match, bring back the most recent date for that person. Summary sheet(requires formula): 09/10/2008 Name Yesterday's numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/08/2008 26 27 28 29 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining MATCH & LOOKUP formulas
This is a great resource; definitely worth a look:
http://www.contextures.com/xlFunctions03.html Regards, Ryan--- -- RyGuy "Txlonghorn76" wrote: Duke, It worked! Thanks! "Txlonghorn76" wrote: Here is the formula: =INDEX(Sheet1!B2:F9,MATCH(A1,Sheet1!B3:B9,0),MATCH ("AAA",Sheet1!C2:F2,0)) I am getting 09/09/2008 in the cell. "Txlonghorn76" wrote: I am getting a #NAME? error. "Duke Carey" wrote: "Dates" is the range of dates "Names" is the range of names "Tbl" is all the other data under Names and to the right of Dates "Thisday" is the cell with today's date "Tgt" is the name to find =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) "Txlonghorn76" wrote: I am trying to find a formula that matches the "Names" from my summary sheet to a separate worksheet and if the names match, bring back the most recent date for that person. Summary sheet(requires formula): 09/10/2008 Name Yesterday's numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/08/2008 26 27 28 29 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combining LOOKUP and IF functions | Excel Worksheet Functions | |||
| Combining LOOKUP and COUNTIF functions | Excel Worksheet Functions | |||
| Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
| Combining formulas | Excel Discussion (Misc queries) | |||
| Combining data from worksheets - lookup? | Excel Discussion (Misc queries) | |||
Hybrid Mode
