Can't use SUM(IF()) for multiple condition lookup if target is text (XL2003)

[elihubogan]

In context, imagine you have a table with Columns (A) City, (B) State, and (C) Population. I'm familiar with arrays like this to return the population for each city/state combination:

{=SUM(IF((CityA=$A$Column)*(StateB=$B$Column),$C$Column,"Error"))}

When Columns A and B are unique combinations, I've used this as a multiple condition lookup as an alternative to vlookup.

However, when I try to run it where Column C's values are text, not numbers (say the name of the Mayor), obviously the SUM(IF()) won't work. I tried the same function like this:

{=IF((CityA=$A$Column)*(StateB=$B$Column),$C$Column,"Error")}

This is also a fail. Any ideas? I know there are workarounds (create an ID="CityA"&"CityB" then just do =VLOOKUP(ID,$ID$Column,2,false), but I don't like not having a more elegant solution.

 

[barry houdini]

Hello elihubogan, welcome to MrExcel, try LOOKUP like this

=LOOKUP(2,1/(CityA=$A$Column)/(StateB=$B$Column),$C$Column)

or an array formula

=INDEX($C$Column,MATCH(1,(CityA=$A$Column)*(StateB=$B$Column),0))

confirmed with CTRL+SHIFT+ENTER

If there are multiple matches then LOOKUP finds the last, INDEX/MATCH the first......

 

[elihubogan]

Thanks for the quick reply! I tried plugging in your formulas, and the index array worked great. I think I lost something in translation with the Lookup formula though (probably b/c I made up that silly notation).

if my sum(if) looks like this:

=SUM(IF(($A$6:$A$10=F6)*($B$6:$B$10=G6),$C$6:$C$10,"error"))

The following throws a formula error b/c I think there's division by 0:

=LOOKUP(2,1/F6=$A$6:$A$10)/(G6=$B$6:$B$10),$C$6:$C$10)

So I tried this, which returned values, just not the right ones:

=LOOKUP(2,(1*(F6=$A$6:$A$10)+1*(G6=$B$6:$B$10)),$C$6:$C$10)

Could you clarify? Thanks so much for your help, the index solution is indeed elegant!

 

[T. Valko]

Thanks for the quick reply! I tried plugging in your formulas, and the index array worked great. I think I lost something in translation with the Lookup formula though (probably b/c I made up that silly notation).

if my sum(if) looks like this:

=SUM(IF(($A$6:$A$10=F6)*($B$6:$B$10=G6),$C$6:$C$10,"error"))

The following throws a formula error b/c I think there's division by 0:

=LOOKUP(2,1/F6=$A$6:$A$10)/(G6=$B$6:$B$10),$C$6:$C$10)

So I tried this, which returned values, just not the right ones:

=LOOKUP(2,(1*(F6=$A$6:$A$10)+1*(G6=$B$6:$B$10)),$C$6:$C$10)

Could you clarify? Thanks so much for your help, the index solution is indeed elegant!

The highlighted formula is missing a ( immediately after the 1/.

=LOOKUP(2,1/(F6=$A$6:$A$10)/(G6=$B$6:$B$10),$C$6:$C$10)

 

[elihubogan]

Thanks! Works great.

 

[T. Valko]

Thanks! Works great.

You're welcome. Thanks for the feedback!