Lookup help

[Guest]

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]

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 here:
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).