Lookup help
Hi Helpppp!
Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Monica |
Lookup help
Is that budget figure always numeric?
If yes, you can use: =sumproduct(--(sheet2!$a$6:$a$51=a6), --(sheet2!$b$6:$b$51=b6), (sheet2!$i$6:$i$51)) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html This actually returns the sum all the rows of column I where column A and column B match the criteria (a6 and b6). Cinny wrote: Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Monica -- Dave Peterson |
All times are GMT +1. The time now is 12:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com