Forum Discussion

Shawn_Jankowski's avatar
Shawn_Jankowski
Copper Contributor
Oct 10, 2025

Excel Filter Formula Help

MS Support Community,

I have a search field that looks for specific data in a large amount of data.  I have the users only able to view the columns that they need by using the CHOOSECOLS function.  The formula has multiple search options and if nothing matches it is supposed to put "No Vessel Found" in the field, but it is just giving me a "VALUE! error in the field.

The formula is below and I have been kicking myself trying to figure this out...any help would be appreciated.

=CHOOSECOLS(FILTER(Vessel_List,ISNUMBER(SEARCH(C5,Vessel_List[IMO Number]))+ISNUMBER(SEARCH(C5,Vessel_List[Vessel Name]))+ISNUMBER(SEARCH(C5,Vessel_List[Linked to (if known)])),"No Vessel Found"),3,4,8,9).

Thank you in advance for your assistance!

Shawn

 

excel
Excel for web
Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 14, 2025

    If nothing matches FILTER returns single text value. Applying CHOOSECOLS(..., 3,4,8,9) to single value returns #VALUE! error - formulae simply can't find columns listed. Only CHOOSECOLS(..., 1) works if no matches.

    Alternatively you may use something like

    =IFERROR( CHOOSECOLS( FILTER(...), ....), "nothing was found" )

Resources