Formula to lookup info in a table - part 2

[James]

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?

 

[Ken Wright]

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)

 

[LEB]

There is an Intersect method in Visual Basic that
determines if two ranges intersect each other.

 

[JE McGimpsey]

Not in XL (there is in VB).

However there is an intersection *operator*.

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