Forum Discussion
Formula Assistance
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)}
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.