You could do something like this instead:

=SUMPRODUCT(--(A2:A6="Lighting"),--(B2:B6="Desk Lamp"),--(C2:C6="LED"),D1:D6)

Or, if you have EXCEL 2007, you could do:

=SUMIFS(D2:D6,A2:A6,"=Lighting",B2:B6,"=Desk Lamp",C2:C6,"=LED")

Otherwise, if you want to do it with some type of lookup function, I
think you'll need to create a "work" column that concatenates the
three lookup fields, then use that column as your lookup key.

On Sun, Jan 11, 2009 at 5:14 PM, smbyrne69 <smbyrne69@...> wrote:
> I am trying to match based on criteria in 3 columns. I have been using
> the CONCATENATE to match in multiple columns.
>
> Dept Type Category Qty
> Lighting Floor Lamp Standard 10
> Lighting Desk Lamp LED 12
> Electrical Electrical Plugs Contractor 15
> Electrical Power Strips 6 pin 20
> Power Tools Drill 18v 5
>
> If I wanted to find the qty for LED Desk Lamp I would
> index(d2:d6,match("lighting"&"Desk
> Lamp"&"LED",CONCATENATE(A2:A6,B2:b6,C2:c6),0),4))
>
> Is there another way besides the CONCATENATE funtion to achieve the
> same result like if statements? I do not want to use the indirect or
> offset function in the formula.