Only First of Multiple Search Functions Working Inside of Filter Formula???

[c.clavin]

Hi guys, I'm running into a weird issue where I have 2 Search functions in a Filter function formula and it's only returning the results of the first Search function. Do you have any idea what's going on here? This is my formula. I tested them each independently, and they return the proper results. Any help would be greatly appreciated. Thanks!

=FILTER('ITEM LUT'!EQ3:EQ100,(ISNUMBER(SEARCH('VARIABLE LUT'!$B$7,'ITEM LUT'!$EP$3:$EP$100)*(ISNUMBER(SEARCH('VARIABLE LUT'!$B$29,'ITEM LUT'!$EP$3:$EP$100))))))

 

[KRice]

I believe the right parentheses on your first ISNUMBER is misplaced...should be after the first 'ITEM LUT'!$EP$3:$EP$100)...check both right parentheses for that 1st expression.

=FILTER('ITEM LUT'!EQ3:EQ100,
(ISNUMBER(SEARCH('VARIABLE LUT'!$B$7,'ITEM LUT'!$EP$3:$EP$100)))*
(ISNUMBER(SEARCH('VARIABLE LUT'!$B$29,'ITEM LUT'!$EP$3:$EP$100)))  )

 

[c.clavin]

That was it. Perfect THANK YOU!!

 

[Peter_SSs]

I'm guessing a bit here but is this formula on the 'VARIABLE LUT' worksheet?

If so, you could/should shorten that formula by removing references to that worksheet. Other shortening included here as well

=LET(EP,'ITEM LUT'!$EP$3:$EP$100,FILTER('ITEM LUT'!EQ3:EQ100,ISNUMBER(SEARCH($B$7,EP)*SEARCH($B$29,EP))))

If I was wrong about the worksheet the formula is on then still ..

=LET(EP,'ITEM LUT'!$EP$3:$EP$100,FILTER('ITEM LUT'!EQ3:EQ100,ISNUMBER(SEARCH('VARIABLE LUT'!B7,EP)*SEARCH('VARIABLE LUT'!B29,EP))))

 

[KRice]

You're welcome...we're happy to help. If you are interested in trimming down the formula, have a look at Peter's suggestion. At a minimum, you could eliminate some of the redundancy since you are searching the same range (so that gets defined once with a short name like EP), and then the two search arrays can be multiplied together before converting them to TRUEs and FALSEs...which will produce a single array consisting of errors and some numbers. Then the resultant array is converted into an array of TRUEs and FALSEs with ISNUMBER, thereby eliminating one of the ISNUMBER operations.

 

[Peter_SSs]

@c.clavin
I notice that you have visited this thread since my previous post but not answered or commented on my question about the sheet name that the formula is on. If it is on the 'VARIABLE LUT' worksheet then I want to point out that leaving that sheet name out of the formula is not just so the formula is shorter, but including the sheet name of the sheet that a formula is on is actually a bad idea and can lead to incorrect results in some cases.

 

[c.clavin]

@c.clavin
I notice that you have visited this thread since my previous post but not answered or commented on my question about the sheet name that the formula is on. If it is on the 'VARIABLE LUT' worksheet then I want to point out that leaving that sheet name out of the formula is not just so the formula is shorter, but including the sheet name of the sheet that a formula is on is actually a bad idea and can lead to incorrect results in some cases.

Hi, thanks for your post. I will review all of these references in the workbook!