Formula that works in excel but not in Google Sheets

[jeremypyle]

Hi,

The following formula works in excel but not in Google Sheets. How do I modify to work in Google Sheets:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",
LOOKUP(999999999999999,SEARCH(abbreviations!$A$1:$JZ$1,'Invoice Insert'!F110),abbreviations!$A$3:$JZ$3)))


I basically want to search a range and if any of the range is contained within my cell, then I want that result. For example in 'Invoice Insert'!F25 I have: 21c Cartwright Road

*62a*A	*62b*A	8b*Cam*	8a*Cam*	21c*Cart*	21 Cart*	21b*Cart*	13a dak*
*62a*A	*62b*A	8b*Cam*	8a*Cam*	21c*Cart*	21 Cart*	21b*Cart*	*13*dak*
62a Arcus	62b Arcus	8b Campbell Pl	8a Campbell Pl	21c Cartwright Rd	21 Cartwright Rd	21b Cartwright Rd	13a Dakota Pl

Since 21c Cartwright Road would be found in within row 1 (it would be found in 21c*Cart* which is e5, then I want to return the corresponding cell in row 3 which would be: 21c Cartwright Rd

This formula works in excel. But you can't seem to use Search to search an entire range for a cell. Please note that the range contains "*" so it isn't necessarily an exact match

This is a very hard formula to work out in Google Sheets. Any help would be much appreciated