Formula Assistance

[Brittany Lopez]

Hello Everyone!

I am using the following formula:

{=IFERROR(INDEX($D:$D,MATCH(1,SEARCH("*"&$D:$D&"*",A1),0)),"")}

I need it to search the entirety of column D, but if there is a blank cell it stops. I don't want to place specifics such as D1:D34 because I use the spreadsheets daily and the one I reference is constantly changing. Prior to the above formula I was using this one: =LOOKUP(2,1/SEARCH(D1:D3,A1),D1:D3). But if there were any blank cells it wouldn't even start searching or provide even one result.

Below is an example I am using for the first formula:

ABC CAT TYPYUTYU	CAT		DOG
XYZ CAT LMG	CAT		CAT
TYPRRT DOG SDK	DOG
LMG PIG ABCYU	0		PIG
SDK DOG XYZ	DOG
ABCASDF DOG TYP	DOG
XYZ CAT LMG	CAT
TYP PIG SDK	0
LWE DOG ESTSDG	DOG
SDK PIG XYZ	0
ABC CAT TYPYUTYU	CAT

I would greatly appreciate any and all help. Thank you! 

[#2]

If you use array formula, $D:$D will make your formula run very slowly.

{=IFERROR(INDEX($D$1:$D$50,MATCH(0,SEARCH(IF(LEN($D$1:$D$50)=0,NA(),$D$1:$D$50),A1)*0,0)),"")}

Make the empty cells in column D to #N/A, and you don't use wildcard characters because it makes the formula slow too.  If you don't use wildcard characters, search function will always return the string position that match function not working.  However, mis-matched item and empty cells will result in #VALUE! or #N/A.  Both of them will be ignore by match.  Also, we do not care about the string position. Hence, "* 0" can make any matching values to 0.  Match function, now, can just need to check if any 0 (matching item).

I reduce the size from the whole column D to $D$1:$D$50.  You need to adjust its size to fit your needs.  

[#3 · Accepted solution]

Brittany,

as @Willy Lau already mentioned your model has two flaws: Searching an entire column and blank entries for you search criteria.

If you want to use range reference that changes from time to time you can convert it into an Excel Table and use structured references.

If your search criteria are manual input then just avoid blank cells.

And here is another formula to cope with blank cells:

{=LOOKUP(9^99,SEARCH(IF($D$1:$D$5="",NA(),$D$1:$D$5),A1),$D$1:$D$5)}

[#4]

Thanks, @Detlef Lewin.  I don't know much about LOOKUP function.  In this case, it is much better than index&match.  

[#5]

@Willy Lau, as always there is more than one way to solve a problem in Excel.