Lookup formula that doesn't have a fixed array (partial match)

[feni1388]

Hello everyone.
I have a list of complete address (including street number) on column A and another list from warehouse that only includes States and City on column F.
I need to check whether the complete address on column A is included on the list that I got from the warehouse.

I currently use this formula and it's OK if the list from the warehouse doesn't change.
However, the list can be shorter or can be longer and I need to include this formula into VBA code that I'm going to make and the problem with this formula is that
the search array has to be fixed.

=LOOKUP(2,1/(ISNUMBER(SEARCH(F2:F10,A2))),(G2:G10))

I tried using countif but it doesn't work either.
Can anyone give any suggestion?

Thank you.

 

[arthurbr]

You can make your range an Excel Table which will accommodate for adding/deleting rows. How that translates in VBA I do not know

 

[feni1388]

That might be a good idea.
Thank you. Let me think about it again.

 

[arthurbr]

You're welcome