Matching multiple criteria on a row then showing a cell on that row

Hi all,
I've been trying to get this to work for some time, basically I've got an array of data about 30x1500 cells in size, I want to use this data in a table I'm making on another worksheet.
Normally I'd just use VLOOKUP and this works great in most cases but I want to do something a little bit more complicated.
Bassically I want to (in normal English) have excel look at the table, find a cell that matches criteria in this data array, then compare to another cell on the same row in this array. If the criteria for these two match then display the data from another cell on the same row. The criteria for matching the first cells would be an exact value, both being strings of text.

I've tried using a couple of nested VLOOKUP and IF statements but failed miserably... anyone know of a way to go about this?

VLOOKUP won't work, as it always returns the first match based on a single criteria.

If your criteria were in columns A & B and you wanted to return column C then you'd want something like this:

=INDEX(C:C,SMALL(IF((A1:A1500="Criteria1")*(B1:B1500="Criteria2"),ROW(A1:A1500)),1))

Confirmed with Ctrl-Shift-Enter, not just Enter.

But without an example worksheet it's hard to give you more specific advice.

Thanks Andrew, I'll give this a try later.
For reference purposes, here's a sample of my array (attached).

Ok so gave this a go and I get a value with no error but not the value I was expecting...

The criteria for the above attachment would be for example:

"Criteria1 = Product1"
"Criteria2 = SALES"

So replacing these and using the correct row values, it does not seem to work sadly

To make sure I got this right, here's what I have; interestingly if you look at the formula analysis, it does actually come up with the right value but the cell displays 0....
My revised formula is this:
=INDEX(E:E,SMALL(IF((A1:A1500="Product1")*(D1:D1500="SALES"),ROW(A1:A1500)),1))

And the formula analysis looks like this:
formula_analysis.PNG

So this is showing 5 which is correct; yet the cell displays 0, any idea why this might be happening?

You have entered the formula with Ctrl-Shift-Enter, haven't you?

If you have then it should appear in the formula bar with curly brackets around it:

{=INDEX(E:E,SMALL(IF((A1:A1500="Product1")*(D1:D1500="SALES"),ROW(A1:A1500)),1))}