Return a value if a number is in a range, even, and then matches the street

Hi guys,

I've searched for an answer, but cant find quite what I'm looking for.

Basically, I have a huge list of addresses with ID numbers, and a list without the ID numbers. An example would be;

2-10 (evens), test road, ID123.

I need to identify if the address is on the street, is in that block of flats (i.e between 2-10, and even number), and then return the ID.

I've already split the number between two cells, but then its all gone a bit wrong.

What would be the easiest way to go about this?

Thank you for your help.

Ok, I've attached an example now

I hope I can get this to work.

Thanks for your response.

Its returning the Street name, as opposed to the ID.

Other than that it seems to be working perfectly.

I'll have a property, for instance, 3 Test Road, but no ID number. In another sheet, I'll have a list of blocks of flats with a relevant ID. I.e block of flats 1-9 (odds) will obviously have 1,3,5,7,9. All of these flats will share the same ID. I then need to assign that ID to 3 Test Road.

Column B already has the street name? I'm going to lose the street name if I enter it there?

I've entered it as an array formula too, I'm getting the {}, but its returning "Test Road" on all of the rows?

You're a real life super hero.

I assume you wear your briefs on the outside of your pants?

Thank you!

Thanks AZ-XL, the only thing your formula didnt do, was take into account the street name..