Creating a dynamic array to search for specific entries based on multiple criteria

Hey everyone,

I've been trying to create a dyamic array that allows me to seach for specific entries based on multiple criteria (sounds like a mouthful, I know). I've been following along with this tutorial:

http://chandoo.org/wp/2014/11/10/for...eward-part-ii/

For somereason, this fourm won't let me post formulas (keeps telling me there's an error and that HTML is not allowed), but allow me to try an explain what is going on with the workbook.

I think understand everything that they article says, but when I copy their formulas and data into my own workbook, it does not procude the same result. I've attached a wookbook to demonstrate.

When I use their formula in row 2, it gives me the result "Apple" because it's just showing me the value column D for row 2. If I copy the fomula down I get the result #NUM! until a certain point where the result changes to #NAME?

I've found that if I change the last reference in the formula (the reference to cell "C1" to an absolute reference ($C$1), I can then drag the fomula down to show the value entered in cells D3-D6. After that I still get #NAME? as the result.

Can anyone help me figure out what is wrong with the formula? Because it would be super useful to me in creating my own dynamic array.

I'm using excel 2007 if that makes a difference?

hope this helps

That solved my problem. It was so simple I didn't think about it.

Thanks guys