Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up part of serial number and copy back associate info | Excel Worksheet Functions | |||
Lookup info in one Column and then returning info in other columns | Excel Worksheet Functions | |||
Type in Part number which pulls info from another wksheet | Excel Worksheet Functions | |||
enter info in table...possible formula | Excel Discussion (Misc queries) | |||
Formula to lookup info in a table | Excel Programming |