Searching for a substring from an array within a string (in right to left order)

[God of Thunder]

Hi, I’ve been lurking here for years now – so thanks for all the help!

I’m currently validating customer address information in our system, and I’ve run into an obstacle checking for proper street abbreviations. I’ve been using this formula at present (probably lifted from an older post on this forum).

=IF($Y16="","",IFERROR(LOOKUP(2^15,SEARCH(Valid_Street_Types!$A$3:$A$143,$Y16),Valid_Street_Types!$A$3:$A$143),"[ERR]"))

The referenced column on ‘Valid_Street_Types’ contains values like “Avenue”, “Boulevard”, and so on.

This looks for any of the full street names within the Street field. I also use a similar formula with a list of correct abbreviations (just looking at a different column on ‘Valid_Street_Types’, with values like “AVE”, “BLVD”, etc).

This has worked pretty well, but I’m hoping to eliminate false positives from unfortunately-named streets. For example: 555 Greenwood Dr. I’d like to confirm that "Dr" is present, but instead it finds the valid abbreviation "GREEN" first. Makes sense, since SEARCH is working from left to right.

So my thought was, searching from right to left would find “Dr” before “Green”. In general the clause I want to identify will be at or near the end of the string. So I reversed the string, reversed my abbreviations, and checked that way. And it still finds Green! (well, NEERG).

So at present I have this value in AL16:

rD doowneerG 555

And I’m hoping to find "rD" with the following (Column G contains my reversed abbreviations).

=IF($AL16="","",IFERROR(LOOKUP(2^15,SEARCH(Valid_Street_Types!$G$3:$G$143,$AL16),Valid_Street_Types!$G$3:$G$143),"[ERR]"))

And it returns NEERG without fail, just like it found GREEN before I did the whole reverse thing. I’ve tried “FIND” instead of search (compensated for case when doing this) with no change.

Anyone have any insight into where I’m going wrong?

 

[XOR LX]

Makes sense, since SEARCH is working from left to right.

But in this use LOOKUP works "from right to left"; that perhaps is your issue.

For example, if cells A7 and A11 in the Valid_Street_Types tab contain "Green" and "Dr" respectively, then:

LOOKUP(2^15,SEARCH(Valid_Street_Types!$A$3:$A$143,$Y16),Valid_Street_Types!$A$3:$A$143)

will resolve to (for example):

LOOKUP(2^15,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;5;#VALUE!;#VALUE!;#VALUE!;15;...},Valid_Street_Types!$A$3:$A$143)

which is "Dr", since LOOKUP returns the value corresponding to the LAST numerical value in the lookup_vector (15) less than or equal to the lookup_value (2^15).

If you want to prioritise returns in cases of more than one search string being found, as here, you should order your Valid_Street_Types list in increasing order of significance, not decreasing.

Regards

 

[God of Thunder]

Ah, thanks - that makes a lot of sense. The issue is with the way lookup works, not search.

I'll step back from my (attempted) solution then - do you have any advice on how to find the first valid lookup result from the string ("first" meaning position within the string)?

Thanks again.