HOW TO MAKE IF AND SEARCH STATEMENT GOOGLE SPREADSHEETS-CodePudding

So i have dropdown (cells J23) and when the dropdown gets the number, i want to show the results number (which is percent cell). I was use filter, search and if function.

When i was run the if function, it's work. But when i was combine it, it doesn't work.

Here's my function

=filter(F10:G,search(J23,IF(J23 < 10, J23, IF(J23 = 10, 10, IF(J23 > 10, J23))), F10:F))

If you need my excel, you can access my google sheets

CodePudding user response：

Your spreadsheet goal is hard to understand from your current formula. I think that you want the following...

In K21:

=IFERROR(VLOOKUP(J21,F6:G8,2,FALSE))

In K23:

=IFERROR(VLOOKUP(J23,F10:G,2,FALSE))

CodePudding user response：

try:

=ARRAYFORMULA(IFERROR(QUERY(TO_TEXT(A5:C), "where 9=9 "&
 IF(I6="",," and lower(Col1) contains '"&LOWER(I6)&"'")&
 IF(J6="",," and lower(Col2) contains '"&LOWER(J6)&"'")&
 IF(K6="",," and lower(Col3) contains '"&LOWER(K6)&"'")), "no match"))