Merging Excel files on a keyfield

Question

Using the answer to this question as my starting point, I have a slightly different scenario that I'm stuck on.

Consider:

File 1: Incident# (key), foo1, foo2, foo3

File 2: Incident# (key), status, foo3, foo4, foo5, foo6

Other considerations:

An incident number may appear in File 1, but not File 2 (no status on incident; assumed open and unassigned).

An incident number may appear in File 2, but not File 1 (an incident that opened prior to the data's scope, but resolved after.

Right now, I'm tring to create File 3: by doing a VLOOKUP on Incident#, and I would like the following fields to appear, based on the following rules.

1. Incident# (if incident# is in File 1 but NOT file 2, populate status with 'pending'. if incident# is in File 2 but NOT file 1, append the record to the file.
2. foo1
3. foo2
4. foo3 (if status is not null, then UPDATE the value for foo3 to the value from File2
5. foo4
6. foo5
7. foo6

However, because my result file is based on comparing values from BOTH sets of keyfields (not jsut a 'one-way' lookup), I fear I'll end up with Circular References and inaccurate data.

Any hints on how to make this happen?

Answer 1

Although Scott proposed a great solution (which I've got bookmarked 'cause I know I'll need it in the future...), we ended up being able to complete this with 'VLOOKUP' because the user suddenly "discovered" additional data columns in the excel files AND changed their requirements.