Hi all -
I've been able to figure out quite a bit of my initial questions, but there are still two areas that I just can't wrap my head around.
I have two spreadsheets, call them "Office" and "Global". Office has just the Mississippi office employee's information, Global has all locations employee's information. I want Global to check Office if, and only if, the Global/location cell has Mississippi. If Global cell C15 shows Mississippi, then it should check Office, find that first and last name in columns B & C - if there is a match, go to column D in Office and copy the ID# there, then paste it into Global D15.

I've managed to get a tiny section of what I want working after two days of reading these forums. lol I'm using =INDEX([Office.xls]Sheet1!$D$1:$D$1000,MATCH(1,([Office.xls]Sheet1!$B$1:$B$1000=A15)*([Office.xls]Sheet1!$C$1:$C$1000=B15),0)) with the ctrl+shft+enter

That gets the information and pastes it, but it's checking every row instead of just the ones that are located in Mississippi - I don't know how to put that in there. Also, instead of #N/A, I'd like to have it show "Unknown" - the ID number is applicable, but isn't known at this time. Again, the most important part of this is that Global only check Office if Global shows Mississippi in the location column (column C). If Global shows Mississippi, then do the Index/Match, returning "Unknown" if no match is found. I look forward to y'all's responses.


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've had my question up on two different forums for two days and basically no answers yet. LoL, I've edited this post because some of what I was trying to accomplish has, in fact, been figured out. It's still not what I want though, not without those other two pieces ...

Ky

I now know how to keep the formula from changing it's reference when I drag, thanks to this forum. Still trying to figure out the two questions above though.
Ky

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

Sorry, this line should read:
Fix all the ranges with $ signs, eg: $M$1:$M$1000
You need the $ signs for the rows, too
(missed out earlier)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Woohoo! Finally got it ...

=IF(C233="FHHA", IF(ISERROR(MATCH(A233 & B233, INDEX([FHHA.xls]Sheet1!$B$6:$B$500 & [FHHA.xls]Sheet1!$C$6:$C$500, 0), 0)), "Unknown", INDEX([FHHA.xls]Sheet1!$M$6:$M$500, MATCH(A233 & B233,INDEX([FHHA.xls]Sheet1!$B$6:$B$500 & [FHHA.xls]Sheet1!$C$6:$C$500, 0), 0))), "")

is what it finally came down to. It looks (to me) like it's doing the match thing twice, which could be bad if this thing gets way longer, but for now it's perfect.

Thanks much!
Ky