Return value in an array based on 2 ifs

**Spitinyari** · 01-26-2012, 07:38 AM

Hi, I've been searching around, but cannot find what I'm after unfortunately, so thought I would post.

I need to look through an array of values, find where two values match specific criteria and return a value from a third column.

For example, take this table:

[pre]
A B C D E F
No Fish Blue 0 F 1
Yes Fish Red 1 V 2
Yes Lamb Green 0 B 3
No Beef Red 1 N 4[/pre]

When column B is "Fish" and column D is 1, I want to know what is shown in column F.

The formula I am using (with ctrl+shift+enter) is:

=IF($B$2:$B$5="Fish",IF($D$2:$D$5=1,$F$2:$F$5,"None"))

However, this always returns "None", whereas it should return "2".

What am I doing wrong?! Any help would be greatly appreciated, it's quite frustrating...

**OnErrorGoto0** · 01-26-2012, 07:50 AM

You might use
=IFERROR(LOOKUP(2,1/(($B$2:$B$5="Fish")*($D$2:$D$5=1)),$F$2:$F$5),"None")

if using Excel 2007 or later.

**estige** · 01-26-2012, 08:00 AM

Or maybe:
=SUMIFS($F$2:$F$5,$B$2:$B$5,"Fish",$D$2:$D$5,1)

Or if older Excel:
=SUM(IF(B2:B5="Fish",IF(D2:D5=1,F2:F5)))
confirmed eith control/shift/enter

**bookstorelee** · 01-26-2012, 08:13 AM

Maybe you can try this formula, =IF(AND(B1="Fish",D1=1),F1,"") to check each rows. There should not be one formula to solve this problem because if two rows contain "Fish" and "1", how you want the formula to show values for two F columns value.

**Spitinyari** · 01-26-2012, 08:39 AM

I'm happy to return only the first time the criteria are filled, so estige's formula seems to do the trick, thank you so much! I will have to look up SUMIFS, I haven't used those before.

Thank you to you all, you've been a great help.

**OnErrorGoto0** · 01-26-2012, 08:43 AM

That formula adds up all matching rows, rather than returning a specific one, in case that is a problem.

**Spitinyari** · 01-26-2012, 08:48 AM

Ah, I see, good point...

As I just need to know one of the corresponding values, I will use:

=IFERROR(LOOKUP(2,1/(($B$2:$B$5="Fish")*($D$2:$D$5=1)),$F$2:$F$5),"None")

as suggested by OnErrorGoto0, that seems to do the trick.

Thanks again!