Formula to lookup info in a table - part 2
 

Thanks for all your great answers.

Isn't there an Intersect function?
I think this should be:
=INTERSECT(Sales,Salesman,Month)
=INTERSECT(range_name,row_label,column_label)

can I DL a custom function?

Tom:
=INDEX(Sales,MATCH(A1,INDEX(Sales,0,1),0),MATCH(A2 ,INDEX
(Sales,1,0),0))

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

JE McGimpsey
Pivot table:
=VLOOKUP(A1,Sales,MATCH(TEXT(A2,"mmm"),OFFSET
(Sales,,,1,),0),0)




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?

Formula to lookup info in a table - part 2
 

Are you perhaps thinking of the INTERCEPT function, which is related to Chart
axes?

To do what you are trying to achieve on that basis, then select your table,
headers and all and Insert / Name / Create, making sure Top and Left are
checked. Now in any cell you can use =Salesman Month (Note the space in
between)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"James" wrote in message
...
Thanks for all your great answers.

Isn't there an Intersect function?
I think this should be:
=INTERSECT(Sales,Salesman,Month)
=INTERSECT(range_name,row_label,column_label)

can I DL a custom function?

Tom:
=INDEX(Sales,MATCH(A1,INDEX(Sales,0,1),0),MATCH(A2 ,INDEX
(Sales,1,0),0))

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

JE McGimpsey
Pivot table:
=VLOOKUP(A1,Sales,MATCH(TEXT(A2,"mmm"),OFFSET
(Sales,,,1,),0),0)




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?



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004

Formula to lookup info in a table - part 2
 

There is an Intersect method in Visual Basic that
determines if two ranges intersect each other.
-----Original Message-----
Thanks for all your great answers.

Isn't there an Intersect function?
I think this should be:
=INTERSECT(Sales,Salesman,Month)
=INTERSECT(range_name,row_label,column_label)

can I DL a custom function?

Tom:
=INDEX(Sales,MATCH(A1,INDEX(Sales,0,1),0),MATCH(A2 ,INDEX
(Sales,1,0),0))

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

JE McGimpsey
Pivot table:
=VLOOKUP(A1,Sales,MATCH(TEXT(A2,"mmm"),OFFSET
(Sales,,,1,),0),0)




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?

.

Formula to lookup info in a table - part 2
 

Not in XL (there is in VB).

However there is an intersection *operator*.

See "Calculation operators in formulas" in help for details.


In article ,
"James" wrote:

Isn't there an Intersect function?