Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to lookup info in a table
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
=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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to lookup info in a table
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to lookup info in a table
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to lookup info in a table
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to lookup info in a table
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help- Lookup table | Excel Discussion (Misc queries) | |||
Lookup info in one Column and then returning info in other columns | Excel Worksheet Functions | |||
enter info in table...possible formula | Excel Discussion (Misc queries) | |||
What formula can I set up to do a cross lookup in an Excel table? | Excel Worksheet Functions | |||
formula to lookup table in another Worksheet please | Excel Worksheet Functions |