Share via

Combine IF and SORT functions

Anonymous
2025-07-08T14:35:32+00:00

I want to combine these two functions together for a search engine-type Excel I'm working on.

The 1st function I made is to show rows from Sheet2 that correspond to the theme/subtheme a person searches:

=SORT(FILTER('Sheet2'!A2:H499, ISNUMBER(SEARCH($E$3,'Sheet2'!A2:A499))*ISNUMBER(SEARCH($E$4,'Sheet2'!B2:B499)),"No result"),4,-1)

The 2nd function is to take into account the types of documents the person searching is looking for (Type1, Type2 or both)

=IF(F3,FILTER('Sheet2'!A2:A499,'Sheet2'!C2:C499="Type1"),IF(G3,FILTER('Sheet2'!A2:A499,'Sheet2'!C2:C499="Type2"),IF(H3,FILTER('Sheet2'!A2:A499,(('Sheet2'!C2:C499="Type1")+('Sheet2'!C2:C499="Type2"))))))

My idea was the put the 1st function instead of the red text in the 2nd function. But when I try to do that, it says there's a values error. Both functions work when they are separated (although the 2nd function is not that useful by itself since it only shows me one column from Sheet2)

Any help would be appreciated! Showing a redacted version below

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2025-07-08T14:48:45+00:00

Dear jeanneperreault,

Thank you for reaching out to Microsoft Community. My name is Vicky and I'm happy to assist!

I try to test somes and here’s a revised formula that may meet your needs:

=SORT( FILTER( 'Sheet2'!A2:H499, ISNUMBER(SEARCH($E$3,'Sheet2'!A2:A499)) * ISNUMBER(SEARCH($E$4,'Sheet2'!B2:B499)) * ( IF(F3, 'Sheet2'!C2:C499="Type1", FALSE) + IF(G3, 'Sheet2'!C2:C499="Type2", FALSE) + IF(H3, ('Sheet2'!C2:C499="Type1")+('Sheet2'!C2:C499="Type2"), FALSE) ) ), 4, -1 ) What this formula does:

  • Filters rows based on the theme and subtheme entered in cells E3 and E4.
  • Applies additional filtering based on the document type selected via checkboxes or TRUE/FALSE values in F3, G3, and H3.
  • Sorts the filtered results by column 4 in descending order.

If none of the document type options are selected, the formula will return no results. You can wrap it in IFERROR(..., "No result") to handle that gracefully.

Please let us know if you have any trouble!

Warm Regards,
Vicky-I - MSFT | Microsoft Community Support Specialist

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-07-08T15:06:11+00:00

    It works! The only thing I changed was that I deleted the "Type1 and Type2" checkbox since I realized it was confusing the function, and so I deleted this line:

     IF(H3, ('Sheet2'!C2:C499="Type1")+('Sheet2'!C2:C499="Type2"), FALSE)

    This still allows the person to check one, two or both boxes, and the function works as intended.

    Thanks so much!

    Was this answer helpful?

    0 comments