Power Query / Power BI get look for data from another excel workbook

I am trying to combine worksheets from two different workbooks with Power Query and I have trouble doing that.

I do not want to merge the two workbooks.

I do not want to create relationships or "joints".

However, I want to get very specific information for one workbook which has only one column. The "ID" column.

The ID column has rows with letter tags : AB or BE. Following these letters, sepcific numeric ranges are associated. For both AB and BE, number ranges first from 0000 to 3000 and from 3000 to 6000.

I thus have the following possibilities: From AB0000 to AB3000 From AB3001 to AB6000 From BE0000 to BE3000 From BE3001 to AB6000

Each category match to the a specific item in my column geography, from the other workbook: From AB0000 to AB3000, it is ItalyZ From AB3001 to AB6000, it is ItalyB From BE0000 to BE3000, it is UKY From BE3001 to AB6000, it is UKM

I am thus trying to find the highest number associated to the first AB category, the second AB category, the first BE category, and the second.

I then want to "bring" this number in the other query and increment it each time that matching country is found in the other workbook. For example :

AB356 is the highest number in the first workbook.

Once the first "ItalyB" is found, the column besides writes "AB357". Once the second is "ItalyB" is found, the column besides write "AB358".

Here is the one columned worksheet:

Here is the other worksheet with the various countries in geography:

Here is an example of results:

have one column (geography) with I think that this is something which I should work towards: