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.

What should be there instead of the 101, 102 etc?

Sites and Dialplan - Online Help.xlsx

=VLOOKUP(Table1[[#This Row],[Location]],'Dialplan Structure'!A:E,5,FALSE)

Copied down.

Try:

=IFERROR(LEFT(VLOOKUP(C3,Table2,5,0),4)&TEXT(COUNTIF(C$3:C3,C3),"100"),"")

copied down

or if there could be more than 100 repeats of a city, then:

=IFERROR(LEFT(VLOOKUP(C17,Table2,5,0),4)&100+TEXT(COUNTIF(C$3:C17,C17),"000"),"")