This one just seems to stump me!
What I have in the cell is:

=INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet1!B2:B1000,0))

and it gives me the eternal #REF!

What am I JUST NOT GETTING?


Thanks

Hans

Shouldn't the range

Sheet1!C2:C1000

span mutiple columns as well?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
This one just seems to stump me!
What I have in the cell is:


=INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet
1!B2:B1000,0))

and it gives me the eternal #REF!

What am I JUST NOT GETTING?


Thanks

Hans

Try this *array* formula:

=INDEX(Sheet1!C2:C1000,MATCH(1,(D104=Sheet1!A2:A10 00)*(Sheet3!E104=Sheet1!B2:B1000),0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
oups.com...
This one just seems to stump me!
What I have in the cell is:

=INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet1!B2:B1000,0))

and it gives me the eternal #REF!

What am I JUST NOT GETTING?


Thanks

Hans

Thanks Guys....Got it working!!

Hans


RagDyer wrote:
Try this *array* formula:

=INDEX(Sheet1!C2:C1000,MATCH(1,(D104=Sheet1!A2:A10 00)*(Sheet3!E104=Sheet1!B2:B1000),0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
oups.com...
This one just seems to stump me!
What I have in the cell is:

=INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet1!B2:B1000,0))

and it gives me the eternal #REF!

What am I JUST NOT GETTING?


Thanks

Hans

Thanks for the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

wrote in message
ps.com...
Thanks Guys....Got it working!!

Hans


RagDyer wrote:
Try this *array* formula:


=INDEX(Sheet1!C2:C1000,MATCH(1,(D104=Sheet1!A2:A10 00)*(Sheet3!E104=Sheet1!B2
:B1000),0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
wrote in message
oups.com...
This one just seems to stump me!
What I have in the cell is:


=INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet
1!B2:B1000,0))

and it gives me the eternal #REF!

What am I JUST NOT GETTING?


Thanks

Hans