Originally Posted by Max

.. I entered it exactly like you said, and all I get is Value#

You probably didn't **array-enter** it properly as per my earlier step:
Put in L1, array-enter (press CTRL+SHIFT+ENTER):

That's why you got the VALUE error. Try it again. With the formula already
pasted into the formula bar, click inside the formula bar, then press
CTRL+SHIFT+ENTER (instead of just pressing ENTER). Done correctly Excel will
wrap curly braces { } around the formula. Look for these braces in the
formula bar as a visual check that the formula has been correctly
array-entered. If you don't see the braces, then it hasn't been correctly
array-entered.

Anyway, here's an illustrative sample for your reference:
http://www.savefile.com/files/555218
Matching multiple criteria.xls

The multiplication of the various identical size criteria arrays, ie:
($A$2:$A$100=G1)*($B$2: $B$100=H1)*($C$2:$C$100=I1)*($D$2:$D$100=J1)
will produce a resultant array of zeros "0"'s with a single "1", eg:
{0,0,1,0,0}
The single "1" marks the position which satisfies all of the criteria

MATCH(1,{0,0,1,0,0},0) then returns the exact position of the "1" within the
resultant array, ie 3.

INDEX($E$2:$E$100,MATCH(...)) resolves to
INDEX($E$2:$E$100,3)
which returns the corresponding element (ie the 3rd item) within E2:E100 as
the final result.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"frankjh19701" wrote in message
...
Is there any way you can walk me through this formula? I've been trying
it every which way I could and I'm going nowhere. I entered it exactly
like you said, and all I get is Value#. If I'm looking for one
particular value in column A, that is also adjacent to a particular
value in column B, and so on until column D, I want to return the value
that is adjacent to them all in the next column, in this case column E.
I just don't see where your formula can do that. Please help me.