Lookup based on 2 conditions?

G

Gary

How do I lookup a value in column 3 based on values in both columns 1
and 2? All the functions in Excel seem to allow only one condition.
 
G

Gary

I don't think the SUMPRODUCT approach works because you can't do
multiplication with a boolean value.

I've come up with this array formula approach:

=SUM(C2:C32766*IF(A2:A32766="Condition1",1,0)*IF(B2:B32766="Condition2",1,0))
 
P

Peo Sjoblom

Gary said:
I don't think the SUMPRODUCT approach works because you can't do
multiplication with a boolean value.
Click to expand...

Where did you get that from? 1 times TRUE is 1 and 1 times FALSE is 0

no need to involve IF in this

Regards,

Peo Sjoblom
 
P

Paul Corrado

Gary,

Not sure what you mean that you can't multiply with 1 & 0 (Boolean values).
Last I looked those numbers worked just like the rest of the Real numbers.

PC
 
P

Paul Corrado

Gary,

Copy & paste the exact formula you are using and also give an example of
your data set. (2 or three lines should be sufficient.)

PC
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autonumber field in Excel that behaves the same as Access 0
Returning 1 of 4 values if conditions are met 4
HLOOKUP, LOOKUP & MATCH doesn't work. Seems not right. 1
multiple conditions 8
Mail Merge Directory Conditional Page Break 0
Vlookup Function 1
Conditional Formatting based on active Named Range; IF Function to VBA 1
Link a text box based on drop down box with four options MS Word 365 0

Top