Forum Discussion

Copper Contributor

Feb 23, 2018

Solved

Formula Assistance

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 bla...

Formulas & Functions

Need Help

Detlef_Lewin
Feb 23, 2018
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)}

Detlef_Lewin

Silver Contributor

Feb 23, 2018

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)}

Willy Lau

Steel Contributor

Feb 23, 2018

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

Detlef_Lewin
Silver Contributor
Feb 23, 2018
Willy Lau, as always there is more than one way to solve a problem in Excel.