Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I create a multiple LOOKUP formula with 'then' function???
Hello there - I hope you can help. I am fast losing my hair on this one!
I have a data sheet in Excel 2000 that I cannot alter or sort. As per my example below it contains numerous rows that contain a list of customers. Under each customer it contains product by product data that is then sorted by column into monthly sales and then totals. My problem is that I need a LOOKUP formula that is able to say "Lookup row with Customer A, THEN LOOKUP the next row that contains 'product B', THEN find the column in that row that gives TOTAL". Ambitous maybe. I have bought 3 Excel books that do not give the solution so I hope it is something that comes with experience not books! Each customer has the same list of products so I am struggling to locate a multiple formula that finds the NEXT "product A" after a particular customer. Example data AXIS / col a / col b / colc / col d / col z row 1 / customer a / blank / blank / blank / col z row 2 / product a / blank / blank / blank / col z row 3 / blank / blank / blank / blank / col z row 4 / blank / blank / jan / feb / col z row 5 / blank / sales / 100 / 200 / TOTAL row 23 / customer g / blank / blank / blank / col z row 24 / product a / blank / blank / blank / col z row 25 / blank / blank / blank / blank / col z row 26 / blank / blank / jan / feb / col z row 27 / blank / sales / 200 / 300 / TOTAL Summary: Is there a formula I can use that ignores the empty cells and can locate on a data sheet a particular customer name in column A, then the next particular product also in column A, then a particular attribute (sales or revenue) which is in column B, then return from that row the value in column Z (TOTAL). Thank you in advance for your help on this problem. I have seen how much advice has been given on the other topics and think this community is fantastic. -- Matthew Cardiff |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I create a multiple LOOKUP formula with 'then' function???
Just to make sure we have a few things straight:
Are you feeding in 2 criteria and returning 2? such as Customer Name, product name and returning Sales/revenue and Total Is the data always in 5 row increments? Is customer name always in the first, product always in the second, and total always in the 5th? Can there be multiple occasions where a customer bought the same product? That should help us answer much quicker and more accurately. -- -John Please rate when your question is answered to help us and others know what is helpful. "Matthew Cardiff" wrote: Hello there - I hope you can help. I am fast losing my hair on this one! I have a data sheet in Excel 2000 that I cannot alter or sort. As per my example below it contains numerous rows that contain a list of customers. Under each customer it contains product by product data that is then sorted by column into monthly sales and then totals. My problem is that I need a LOOKUP formula that is able to say "Lookup row with Customer A, THEN LOOKUP the next row that contains 'product B', THEN find the column in that row that gives TOTAL". Ambitous maybe. I have bought 3 Excel books that do not give the solution so I hope it is something that comes with experience not books! Each customer has the same list of products so I am struggling to locate a multiple formula that finds the NEXT "product A" after a particular customer. Example data AXIS / col a / col b / colc / col d / col z row 1 / customer a / blank / blank / blank / col z row 2 / product a / blank / blank / blank / col z row 3 / blank / blank / blank / blank / col z row 4 / blank / blank / jan / feb / col z row 5 / blank / sales / 100 / 200 / TOTAL row 23 / customer g / blank / blank / blank / col z row 24 / product a / blank / blank / blank / col z row 25 / blank / blank / blank / blank / col z row 26 / blank / blank / jan / feb / col z row 27 / blank / sales / 200 / 300 / TOTAL Summary: Is there a formula I can use that ignores the empty cells and can locate on a data sheet a particular customer name in column A, then the next particular product also in column A, then a particular attribute (sales or revenue) which is in column B, then return from that row the value in column Z (TOTAL). Thank you in advance for your help on this problem. I have seen how much advice has been given on the other topics and think this community is fantastic. -- Matthew Cardiff |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I create a multiple LOOKUP formula with 'then' function???
I have not fully understood the structure of your data but you will
most certainly need OFFSET. The expression: =INDEX(A:A,MATCH(custA,A:A,0)) will locate the cell that contains custA in column A:A. From there you can use OFFSET. For example, a customer's total is 4 rows further down and 4 columns further to the right. Thus, to retrieve the total of custA: =OFFSET(INDEX(A:A,MATCH(custA,A:A,0)),4,4) Does this help? Kostis Vezerides On Oct 17, 4:41 pm, Matthew Cardiff wrote: Hello there - I hope you can help. I am fast losing my hair on this one! I have a data sheet in Excel 2000 that I cannot alter or sort. As per my example below it contains numerous rows that contain a list of customers. Under each customer it contains product by product data that is then sorted by column into monthly sales and then totals. My problem is that I need a LOOKUP formula that is able to say "Lookup row with Customer A, THEN LOOKUP the next row that contains 'product B', THEN find the column in that row that gives TOTAL". Ambitous maybe. I have bought 3 Excel books that do not give the solution so I hope it is something that comes with experience not books! Each customer has the same list of products so I am struggling to locate a multiple formula that finds the NEXT "product A" after a particular customer. Example data AXIS / col a / col b / colc / col d / col z row 1 / customer a / blank / blank / blank / col z row 2 / product a / blank / blank / blank / col z row 3 / blank / blank / blank / blank / col z row 4 / blank / blank / jan / feb / col z row 5 / blank / sales / 100 / 200 / TOTAL row 23 / customer g / blank / blank / blank / col z row 24 / product a / blank / blank / blank / col z row 25 / blank / blank / blank / blank / col z row 26 / blank / blank / jan / feb / col z row 27 / blank / sales / 200 / 300 / TOTAL Summary: Is there a formula I can use that ignores the empty cells and can locate on a data sheet a particular customer name in column A, then the next particular product also in column A, then a particular attribute (sales or revenue) which is in column B, then return from that row the value in column Z (TOTAL). Thank you in advance for your help on this problem. I have seen how much advice has been given on the other topics and think this community is fantastic. -- Matthew Cardiff |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I create a multiple LOOKUP formula with 'then' function???
Hi John - Thanks for the assitance.
I am feeding in 2 criteria to return 1 such as Customer name and product to return Total. The main issue is that the data is at various and ever changing intervals because some customers only order 1 product and others order 7 or 8. The customer name is always in the first row of that set but which product and how many products vary, therefore it is impossible to calculate which cell the total will be in. There is no option to use OFFSET as the data is updated weekly and row positions may change. It is likely that there are multiple occasions where a customer bought the same product. Most likey 90% of customers bought product A then 50% of them will have bought A and B and 25% of them will have bought A,B&C so they will all have identical row titles with the product names under each customer title. Hope this helps. -- Matthew Cardiff "John Bundy" wrote: Just to make sure we have a few things straight: Are you feeding in 2 criteria and returning 2? such as Customer Name, product name and returning Sales/revenue and Total Is the data always in 5 row increments? Is customer name always in the first, product always in the second, and total always in the 5th? Can there be multiple occasions where a customer bought the same product? That should help us answer much quicker and more accurately. -- -John Please rate when your question is answered to help us and others know what is helpful. "Matthew Cardiff" wrote: Hello there - I hope you can help. I am fast losing my hair on this one! I have a data sheet in Excel 2000 that I cannot alter or sort. As per my example below it contains numerous rows that contain a list of customers. Under each customer it contains product by product data that is then sorted by column into monthly sales and then totals. My problem is that I need a LOOKUP formula that is able to say "Lookup row with Customer A, THEN LOOKUP the next row that contains 'product B', THEN find the column in that row that gives TOTAL". Ambitous maybe. I have bought 3 Excel books that do not give the solution so I hope it is something that comes with experience not books! Each customer has the same list of products so I am struggling to locate a multiple formula that finds the NEXT "product A" after a particular customer. Example data AXIS / col a / col b / colc / col d / col z row 1 / customer a / blank / blank / blank / col z row 2 / product a / blank / blank / blank / col z row 3 / blank / blank / blank / blank / col z row 4 / blank / blank / jan / feb / col z row 5 / blank / sales / 100 / 200 / TOTAL row 23 / customer g / blank / blank / blank / col z row 24 / product a / blank / blank / blank / col z row 25 / blank / blank / blank / blank / col z row 26 / blank / blank / jan / feb / col z row 27 / blank / sales / 200 / 300 / TOTAL Summary: Is there a formula I can use that ignores the empty cells and can locate on a data sheet a particular customer name in column A, then the next particular product also in column A, then a particular attribute (sales or revenue) which is in column B, then return from that row the value in column Z (TOTAL). Thank you in advance for your help on this problem. I have seen how much advice has been given on the other topics and think this community is fantastic. -- Matthew Cardiff |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I create a multiple LOOKUP formula with 'then' function???
Thank you for your assistance Vezerid. That solution does not help with this
particular problem but will certainly come in useful with a different dilema! My data is not set in structure. The number of rows containing data underneath each customer is direclty linked to how many products they buy. Some customer who only order 1 product will have a 5 row inclusion, whereas a customer ordering 6 products will have over 30 rows. The data is updated weekly and the structure can change by any customer ordering or not ordering a particular product. I need a formula that is dynamic and can 'search' for the product name following a given customer name. -- Matthew Cardiff "vezerid" wrote: I have not fully understood the structure of your data but you will most certainly need OFFSET. The expression: =INDEX(A:A,MATCH(custA,A:A,0)) will locate the cell that contains custA in column A:A. From there you can use OFFSET. For example, a customer's total is 4 rows further down and 4 columns further to the right. Thus, to retrieve the total of custA: =OFFSET(INDEX(A:A,MATCH(custA,A:A,0)),4,4) Does this help? Kostis Vezerides On Oct 17, 4:41 pm, Matthew Cardiff wrote: Hello there - I hope you can help. I am fast losing my hair on this one! I have a data sheet in Excel 2000 that I cannot alter or sort. As per my example below it contains numerous rows that contain a list of customers. Under each customer it contains product by product data that is then sorted by column into monthly sales and then totals. My problem is that I need a LOOKUP formula that is able to say "Lookup row with Customer A, THEN LOOKUP the next row that contains 'product B', THEN find the column in that row that gives TOTAL". Ambitous maybe. I have bought 3 Excel books that do not give the solution so I hope it is something that comes with experience not books! Each customer has the same list of products so I am struggling to locate a multiple formula that finds the NEXT "product A" after a particular customer. Example data AXIS / col a / col b / colc / col d / col z row 1 / customer a / blank / blank / blank / col z row 2 / product a / blank / blank / blank / col z row 3 / blank / blank / blank / blank / col z row 4 / blank / blank / jan / feb / col z row 5 / blank / sales / 100 / 200 / TOTAL row 23 / customer g / blank / blank / blank / col z row 24 / product a / blank / blank / blank / col z row 25 / blank / blank / blank / blank / col z row 26 / blank / blank / jan / feb / col z row 27 / blank / sales / 200 / 300 / TOTAL Summary: Is there a formula I can use that ignores the empty cells and can locate on a data sheet a particular customer name in column A, then the next particular product also in column A, then a particular attribute (sales or revenue) which is in column B, then return from that row the value in column Z (TOTAL). Thank you in advance for your help on this problem. I have seen how much advice has been given on the other topics and think this community is fantastic. -- Matthew Cardiff |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I create a multiple LOOKUP formula with 'then' function???
You would need programming then. Follow me a little here so i can get a
little more info. If you give me a customer and a product, the output is the total for the entire purchase? Such as customer 1 bought A, B, C and you want to know the total of the entire order where customer =1 and say product =B? If this is the case, is there something that says "Total" or another way to tell where the total is? With the answers to these we could probably whip something up -- -John Please rate when your question is answered to help us and others know what is helpful. "Matthew Cardiff" wrote: Hi John - Thanks for the assitance. I am feeding in 2 criteria to return 1 such as Customer name and product to return Total. The main issue is that the data is at various and ever changing intervals because some customers only order 1 product and others order 7 or 8. The customer name is always in the first row of that set but which product and how many products vary, therefore it is impossible to calculate which cell the total will be in. There is no option to use OFFSET as the data is updated weekly and row positions may change. It is likely that there are multiple occasions where a customer bought the same product. Most likey 90% of customers bought product A then 50% of them will have bought A and B and 25% of them will have bought A,B&C so they will all have identical row titles with the product names under each customer title. Hope this helps. -- Matthew Cardiff "John Bundy" wrote: Just to make sure we have a few things straight: Are you feeding in 2 criteria and returning 2? such as Customer Name, product name and returning Sales/revenue and Total Is the data always in 5 row increments? Is customer name always in the first, product always in the second, and total always in the 5th? Can there be multiple occasions where a customer bought the same product? That should help us answer much quicker and more accurately. -- -John Please rate when your question is answered to help us and others know what is helpful. "Matthew Cardiff" wrote: Hello there - I hope you can help. I am fast losing my hair on this one! I have a data sheet in Excel 2000 that I cannot alter or sort. As per my example below it contains numerous rows that contain a list of customers. Under each customer it contains product by product data that is then sorted by column into monthly sales and then totals. My problem is that I need a LOOKUP formula that is able to say "Lookup row with Customer A, THEN LOOKUP the next row that contains 'product B', THEN find the column in that row that gives TOTAL". Ambitous maybe. I have bought 3 Excel books that do not give the solution so I hope it is something that comes with experience not books! Each customer has the same list of products so I am struggling to locate a multiple formula that finds the NEXT "product A" after a particular customer. Example data AXIS / col a / col b / colc / col d / col z row 1 / customer a / blank / blank / blank / col z row 2 / product a / blank / blank / blank / col z row 3 / blank / blank / blank / blank / col z row 4 / blank / blank / jan / feb / col z row 5 / blank / sales / 100 / 200 / TOTAL row 23 / customer g / blank / blank / blank / col z row 24 / product a / blank / blank / blank / col z row 25 / blank / blank / blank / blank / col z row 26 / blank / blank / jan / feb / col z row 27 / blank / sales / 200 / 300 / TOTAL Summary: Is there a formula I can use that ignores the empty cells and can locate on a data sheet a particular customer name in column A, then the next particular product also in column A, then a particular attribute (sales or revenue) which is in column B, then return from that row the value in column Z (TOTAL). Thank you in advance for your help on this problem. I have seen how much advice has been given on the other topics and think this community is fantastic. -- Matthew Cardiff |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I create a multiple LOOKUP formula with 'then' function???
Yes spot on John. There are multiple uses but predominantly I would want to
input a customer name and a product name and the return would be the total sales for the year for that particular product bought by that customer so far that year. (The TOTAL sales of that product is always in column 'O' it is determining which row to search that is hardest). The 'TOTAL' column is titled 'TOTAL' in Column 'O' which is after many columns with Jan-Dec headings in the same row. Also the 'TOTAL' heading covers 4 different variables. Under 'TOTAL' you have the figures for Total Volume, Litres, Price and Turnover. I would want to enter 'Customer a' & 'product b' & 'total volume' and it return me the figure for just that. The headings for each row: Volume, Litres, Price and Turnover are in column B It is a shame I can't capture a screen and paste it on this post!! Does that make it any clearer? -- Matthew Cardiff "John Bundy" wrote: You would need programming then. Follow me a little here so i can get a little more info. If you give me a customer and a product, the output is the total for the entire purchase? Such as customer 1 bought A, B, C and you want to know the total of the entire order where customer =1 and say product =B? If this is the case, is there something that says "Total" or another way to tell where the total is? With the answers to these we could probably whip something up -- -John Please rate when your question is answered to help us and others know what is helpful. "Matthew Cardiff" wrote: Hi John - Thanks for the assitance. I am feeding in 2 criteria to return 1 such as Customer name and product to return Total. The main issue is that the data is at various and ever changing intervals because some customers only order 1 product and others order 7 or 8. The customer name is always in the first row of that set but which product and how many products vary, therefore it is impossible to calculate which cell the total will be in. There is no option to use OFFSET as the data is updated weekly and row positions may change. It is likely that there are multiple occasions where a customer bought the same product. Most likey 90% of customers bought product A then 50% of them will have bought A and B and 25% of them will have bought A,B&C so they will all have identical row titles with the product names under each customer title. Hope this helps. -- Matthew Cardiff "John Bundy" wrote: Just to make sure we have a few things straight: Are you feeding in 2 criteria and returning 2? such as Customer Name, product name and returning Sales/revenue and Total Is the data always in 5 row increments? Is customer name always in the first, product always in the second, and total always in the 5th? Can there be multiple occasions where a customer bought the same product? That should help us answer much quicker and more accurately. -- -John Please rate when your question is answered to help us and others know what is helpful. "Matthew Cardiff" wrote: Hello there - I hope you can help. I am fast losing my hair on this one! I have a data sheet in Excel 2000 that I cannot alter or sort. As per my example below it contains numerous rows that contain a list of customers. Under each customer it contains product by product data that is then sorted by column into monthly sales and then totals. My problem is that I need a LOOKUP formula that is able to say "Lookup row with Customer A, THEN LOOKUP the next row that contains 'product B', THEN find the column in that row that gives TOTAL". Ambitous maybe. I have bought 3 Excel books that do not give the solution so I hope it is something that comes with experience not books! Each customer has the same list of products so I am struggling to locate a multiple formula that finds the NEXT "product A" after a particular customer. Example data AXIS / col a / col b / colc / col d / col z row 1 / customer a / blank / blank / blank / col z row 2 / product a / blank / blank / blank / col z row 3 / blank / blank / blank / blank / col z row 4 / blank / blank / jan / feb / col z row 5 / blank / sales / 100 / 200 / TOTAL row 23 / customer g / blank / blank / blank / col z row 24 / product a / blank / blank / blank / col z row 25 / blank / blank / blank / blank / col z row 26 / blank / blank / jan / feb / col z row 27 / blank / sales / 200 / 300 / TOTAL Summary: Is there a formula I can use that ignores the empty cells and can locate on a data sheet a particular customer name in column A, then the next particular product also in column A, then a particular attribute (sales or revenue) which is in column B, then return from that row the value in column Z (TOTAL). Thank you in advance for your help on this problem. I have seen how much advice has been given on the other topics and think this community is fantastic. -- Matthew Cardiff |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I create a multiple LOOKUP formula with 'then' function???
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup function for multiple worksheets...and then 'some' | Excel Worksheet Functions | |||
Multiple lookup function | Excel Worksheet Functions | |||
Multiple lookup function | Excel Worksheet Functions | |||
Lookup function for a value that has multiple instances | Excel Worksheet Functions | |||
How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions |