if the left column is Bill, John, Joe
and the column headers are Jan, Feb, Mar.....
and the table is named Sales

A1 is named Salesman and contains John,
A2 is named Month and contains Mar
What is the best formula to lookup John's sales in March?

=INDEX(SALES,MATCH(A1,INDEX(SALES,0,1),0),MATCH(A2 ,INDEX(SALES,1,0),0))

--
Regards,
Tom Ogilvy

"James" wrote in message
...
if the left column is Bill, John, Joe
and the column headers are Jan, Feb, Mar.....
and the table is named Sales

A1 is named Salesman and contains John,
A2 is named Month and contains Mar
What is the best formula to lookup John's sales in March?

Best way:

Pivot Table

Formula way:

=VLOOKUP(A1,Sales,MATCH(TEXT(A2,"mmm"),OFFSET(Sale s,,,1,),0),0)



In article ,
"James" wrote:

if the left column is Bill, John, Joe
and the column headers are Jan, Feb, Mar.....
and the table is named Sales

A1 is named Salesman and contains John,
A2 is named Month and contains Mar
What is the best formula to lookup John's sales in March?

James,

Create 2 more named ranges

SalesMonths - the 12 month cells (not the one to the left above the names)
SalesNames - all of the names, again not the cell above

and then use

=INDEX(Sales,MATCH(A1,SalesNames,0)-1,MATCH(A2,SalesMonths,0)+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"James" wrote in message
...
if the left column is Bill, John, Joe
and the column headers are Jan, Feb, Mar.....
and the table is named Sales

A1 is named Salesman and contains John,
A2 is named Month and contains Mar
What is the best formula to lookup John's sales in March?

shouldn't the first match be +1 vice -1? if the first name were the
match, match would return 1, but this would be Index 2 for the SALES table.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
James,

Create 2 more named ranges

SalesMonths - the 12 month cells (not the one to the left above the names)
SalesNames - all of the names, again not the cell above

and then use

=INDEX(Sales,MATCH(A1,SalesNames,0)-1,MATCH(A2,SalesMonths,0)+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"James" wrote in message
...
if the left column is Bill, John, Joe
and the column headers are Jan, Feb, Mar.....
and the table is named Sales

A1 is named Salesman and contains John,
A2 is named Month and contains Mar
What is the best formula to lookup John's sales in March?

Yeah, you are right. Your answer is better anyway.

Bob

"Tom Ogilvy" wrote in message
...
shouldn't the first match be +1 vice -1? if the first name were the
match, match would return 1, but this would be Index 2 for the SALES
table.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
James,

Create 2 more named ranges

SalesMonths - the 12 month cells (not the one to the left above the
names)
SalesNames - all of the names, again not the cell above

and then use

=INDEX(Sales,MATCH(A1,SalesNames,0)-1,MATCH(A2,SalesMonths,0)+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"James" wrote in message
...
if the left column is Bill, John, Joe
and the column headers are Jan, Feb, Mar.....
and the table is named Sales

A1 is named Salesman and contains John,
A2 is named Month and contains Mar
What is the best formula to lookup John's sales in March?