Thread: Lookup multi columns w/ multi answers

I have two excel files, one a full list of town properties (master) and the second a partial list (pickups) of town properties. The partial list is of yearly work that needs to be completed (reviewed & verified) but the tracking is done on the master list.

1. I need to pull in the year the work is being completed for from the partial list to the master list.
2. I need to track the completion of meas&list (M&L) or meas (M), identified by a 1 in the corresponding cell from the master spreadsheet.

Each property is specifically identified by the Map (A), Lot (B), Block (C) format and is the same for each file.

I cannot concatenate the info because it will cause duplicate responses (ex M11L12B0 & M111L2B0 both create 11120). I want to do both methods to track and verify the completion of all lots in the town over a 4 to 5 year time frame and on an annual basis.

I have the formula that will give me the response from both the M&L and M columns when I concatenate, but I don't know the formula for verifying all 3 MLB columns. Examples of the spreadsheets are attached.
Thanks for the help!

There are a couple of ways to create a unique key for each record. If the values are always numeric, you can use:

Key = Map * 1000000 + Lot * 1000 + Block (adjust the mutipliers so that you are guaranteed at least one zero between each component.)

If not, use:

Key = Map & "-" & Lot & "-" & Block.

Excel handles alpha keys just as well as numeric.

Hope this helps

SAE