That's it!!! Thank you so much, your solution works beautifully. Now I've
just got to pick it apart and try to learn from it.......

"Jim Rech" wrote:

I hope this works better. The essential thing missing was the absolute
signs ("$"). Also I added asterisks between items to reduce the likelihood
of the false match. Remember this formula has to be put on row 2 (like in
cell A2) and entered via Ctrl-Shift-Enter. And then copied down to the rows
below.

=INDEX(Sheet2!$A$2:$A$1000,MATCH(F2&"*"&G2&"*"&K2, Sheet2!$E$2:$E$1000&"*"&Sheet2!$H$2:$H$1000&"*"&Sh eet2!$K$2:$K$1000,0))

--
Jim
"kittybat" wrote in message
...
|I think my follow-up to my original question got buried in the sands of
time
| ; ) so I am re-posting as a new question. I think the solution given
below
| should work, but I can't quite get it to go. I need the help of an expert
to
| "translate" the formula to match my spreadsheet. Please see below for the
| original thread:
| -------------------------------------------
| I think this is the right solution, but I am having trouble getting it to
| work. When I remove all the extraneous columns and match my spreadsheets
to
| the example (ServiceDate is column B, Procedure is column C, and $Billed
is
| column D) on each sheet, the formula pulls the first Claim# for every
line,
| whether it has a match or not. If I change the formula to match the
location
| of my data, the formula returns 0 on each line.
|
| Perhaps if I give you the actual columns for the existing data, there will
| be less room for error on my part.
|
| On sheet1, the columns are ServiceDate - col F, Procedure# - col G,
$Billed
| - col K.
| On sheet2, the columns are ServiceDate - col E, Procedure# - col H,
$Billed
| - col K.
|
| Can you translate the formula to match these data locations? Obviously,
I'm
| doing it incorrectly.
|
| Thank you!
|
| Kittybat
|
| "Bob Umlas" wrote:
|
| Assuming the fields are in columns A:E, then you need this in Sheet1,
cell
| F2, for example (on the Smith,John line):
| Enter this formula by holding Shift+Ctrl before pressing enter:
|
=INDEX(Sheet2!A:A,MATCH(B2&C2&E2,Sheet2!A1:A1000&S heet2!B1:B1000&Sheet2!C1:C1000,0))
|
| Bob Umlas
| Excel MVP
|
|
| "kittybat" wrote in message
| ...
| I work at an health insurance company and frequently need to
reconcile
| reports from our physicians with claim data. I need to find records
on
| two
| different spreadsheets that match on three data points and insert a
claim
| number on matching records. Here's an example:
|
| Spreadsheet 1, from our physician group, contains treatment data for
one
| patient.
|
| PhysicianName ServiceDate Procedure# $billed $paid
| deductible
|
| Smith,John 12/01/03 99213 75.50 25.00
| 25.00
| Doe,Mary 01/25/04 99215 125.00 90.00
| 0.00
|
|
| Spreadsheet 2, from our server, contains claims data for one
patient.
|
| Claim# ServiceDate Procedure# $billed $paid
| deductible
|
| 123456789 12/01/03 99213 75.50 25.00
25.00
| 123687432 01/27/04 99215 125.00 90.00
0.00
|
| I need to find records on spreadsheet 1 that exactly match
spreadsheet 2
| on
| the fields ServiceDate, Procedure#, and $billed. When a match is
found, I
| need the corresponding Claim# inserted after the record on
spreadsheet 1.
| So
| on the example data above, the first row would be a match, because
all
| three
| relevant fields are the same. the second row is not a match,
because the
| ServiceDate field does not match.
|
| I figure that I'll need a combination of MATCH and INDEX, but I
haven't
| been
| able to pin down exactly how to accomplish this. Any help you can
give
| will
| be very much appreciated.
|