multiple lookup formula

[ladfromleeds]

Hi all,

I'm brand new to the site, so please bear with me.

I've been banging my head on a brick wall for a while. I'm needing a formula to lookup a figure from multiple columns. I have highlighted in yellow on the spreadsheet where I need to look up - Name, Catagory & Total.

I hope this make sense??

[Paul]

Hi Lad, welcome to the forum.

What result are you trying to lookup? Are you just trying to match the name and category to get the total, or are you trying to match Name/Category/Total and get some other result?

To get the total based on the lookup of the name/category, try:

=SUMPRODUCT(--($A$3:$A$14="John Smith"),--($B$3:$B$14="C.I."),$H$3:$H$14)

If you wanted to lookup different names or categories, you could type it into a cell rather than hardcoding it into the formula, e.g.

=SUMPRODUCT(--($A$3:$A$14=A1),--($B$3:$B$14=B1),$H$3:$H$14)

Where A1 holds a name and B1 a category.

[ladfromleeds]

Thanks Paul...life saver !