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

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

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

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

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

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

Combining MATCH & LOOKUP formulas
 

Can you guys tell me if there is a way to alter this formula to get MTD
instead of yesterday?

"ryguy7272" wrote:

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

Combining MATCH & LOOKUP formulas
 

Can you tell me if there is a way to add to this formula by asking it to look
at the date and return a sum of that month?

"Txlonghorn76" wrote:

Can you guys tell me if there is a way to alter this formula to get MTD
instead of yesterday?

"ryguy7272" wrote:

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