Excel 2010 - Matching one cell to a list of cells on another sheet

Good morning guys!

I have been trying to find an answer to this all morning and have been stumped.

I have attached a working spreadsheet to this topic because I find it difficult to explain what it is that I need.

So, I am trying to do a few things at once to make this simple for me going forward.

On the first Spreadsheet is a list of Rooms that require an addressing scheme applied to them. On the second sheet is the dialplan scheme in which to apply.

What I am looking for is a formula that will: Look at the 'location' site (eg, Barrie) on sheet1, match it to 'office' location on sheet2, and then grab the associated 'alias prefix' on sheet2, and apply it to column D on sheet1.


if that makes sense?

And, a "Nice to Have" would be that for multiple sites in the same location (ie, multiple systems in Durham location), the numbering scheme would then incrementally go up.

I manually filled in a few entries on sheet1 with what I want to appear.

I have done these types of spreadsheets way too often manually, and I am getting very frustrated with the ammount of time it takes. Does someone out there happen to have a faster way to do this? or perhaps a suggestion in where I could start looking for this answer?


Many Thanks,

V.