Return value in an array based on 2 ifs

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...

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.

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

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!

That will return the last matching value - is the order important?