Did you array-enter* your expression as posted?
*press CTRL+SHIFT+ENTER to confirm the formula

.. I don't want the M1, B1, C1 to change to M85,
etc - I want every cell to look from row 1-1000
Fix all the ranges with $ signs, eg: $M1:$M1000

As for the error N/As, you can suppress it, indicatively:
=IF(ISNA(MATCH(..)),"",INDEX(..))
Array-enter the above expression (as before)
by pressing CTRL+SHIFT+ENTER
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"txheart" wrote in message
...

Good morning,
I posted this question initially at the Mr. Excel forums, but I haven't
received a response, so decided to try here. I am not an advanced user,
though I used to think I was - lol.

I've got 2 workbooks: one is a listing of our entire company's
employee's insurance information; the other is a listing of just this
office's demographical information. My goal is to have the entire
company database, let's call it Global, look at the Office database,
match the names, and if a match exists, to enter the employee's ID
number that is in the Office spreadsheet to a cell in the Global
spreadsheet.

The Global spreadsheet will not match all the names, and they won't be
in the same order - not even close. I would like the Global to check
the other spreadsheet if, and only if, column C says Mississippi. If
Global sees an employee based in Mississippi, then it should check the
Office database to see if a match exists - if a match exists, copy the
ID number from Office and put it into Global.

I messed with this all day long yesterday, and have come up with
=INDEX([Office.xls]Sheet1!M1:M1000,MATCH(1,([Office.xls]Sheet1!B1:B1000=A16)*([Office.xls]Sheet1!C1:C1000=B16),0))
That works, but there's a couple of issues with it.
The first is that this command is checking every name against the other
spreadsheet, resulting in a ton of #N/A that I dont want there.
The second issue is that dragging the formula down changes all of the
cells to the next cell -- With say 280 rows in Global, this dragged
formula, the farther down I drag, the less of the other spreadsheet
it's actually looking at. I don't want the M1, B1, C1 to change to M85,
etc - I want every cell to look from row 1-1000. So, when I drag, I have
to go every 5 cells or so and change all of the references, except the
last one, to 1 - the employee it's trying to match might just be #2 and
dragging that formula down would cause the formula to miss the employee.

The final issue, that I've encountered so far, is that I would rather
not have #N/A on the spreadsheet at all. If it's a Mississippi employee
but there is no name match then I'd like to see "Unknown" rather than
#N/A because that cell is actually applicable, just not known at the
time.

Goodness, I'm wordy! I've tried to explain exactly what I'm trying to
accomplish, but if there are any questions, or if something needs
clarified, I'll certainly try again. I look forward to y'all's
responses.
Ky




--
txheart