Match, then autopopulate, based on condition

T

txheart

Good morning,
I posted this question initially at the Mr. Excel forums, but I haven'
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'
employee's insurance information; the other is a listing of just thi
office's demographical information. My goal is to have the entir
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 I
number that is in the Office spreadsheet to a cell in the Globa
spreadsheet.

The Global spreadsheet will not match all the names, and they won't b
in the same order - not even close. I would like the Global to chec
the other spreadsheet if, and only if, column C says Mississippi. I
Global sees an employee based in Mississippi, then it should check th
Office database to see if a match exists - if a match exists, copy th
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 othe
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 th
cells to the next cell -- With say 280 rows in Global, this dragge
formula, the farther down I drag, the less of the other spreadshee
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 hav
to go every 5 cells or so and change all of the references, except th
last one, to 1 - the employee it's trying to match might just be #2 an
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 rathe
not have #N/A on the spreadsheet at all. If it's a Mississippi employe
but there is no name match then I'd like to see "Unknown" rather tha
#N/A because that cell is actually applicable, just not known at th
time.

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

Max

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 said:
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
 
T

txheart

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 th
match thing twice, which could be bad if this thing gets way longer
but for now it's perfect.

Thanks much!
K
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top