Lookup question matching part of lookup value in the table array

Hi All,
I'm new here, so i will try to be as clear as possible:
I have a column which contains a full address i.e.
285 Charlemont, Griffith Avenue, Dublin 9
262 Larkhill Road, Whitehall, Dublin 9

I have a lookup table that contains
(A1)Estate (A2) delivery day
(B1)Charlemont (B2)Tuesday
(C1)Larkhill Road (B3)Wednesday

How can I add a new column beside the full address that will contain the relevant delivery day?

Please note that changing text to column and doing a vlookup doesn't work because some estates have 2 or more words.

Thanks for help in advance

K

welcome to the forum, kosmo. do upload a sample excel file next time so that we do not have to create a file for testing. assuming your full addresses are in H1, try this:
=IFERROR(LOOKUP(2^15,SEARCH($B$1:$C$1,H1),$B$2:$C$2),"")

glad to help. 2^15 is 2 to the power of 15 & that equates to 32,768. xlsx files allow up to 32,767 characters in a cell. so that's supposed to be a number big enough to ensure i'm able to pick up whatever results the SEARCH formula returns.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.