Forum Discussion

PedroPascual's avatar
PedroPascual
Copper Contributor
Nov 25, 2022

How to exclude a Value from a lookup & array formula

HI All, how i can add an exclusion of a specific value in the following formula??   "=IFERROR (LOOKUP(1,ARRAYFORMULA(1/(A2:m2 <> "")),A2:m2),"")"   this formula takes the last value of the row b...
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 25, 2022

PedroPascual 

I do not think ARRAYFORMULA exists in Excel, so I have omitted it.  In general terms, one includes further divisions that throw errors for the values you wish to exclude

= LOOKUP(1,1/(rowArray<>"")/(rowArray<>90),rowArray)
  • PedroPascual's avatar
    PedroPascual
    Copper Contributor
    Nov 28, 2022

    PeterBartholomew1 thanks for your reply.
    yes, that formula is done in Gsheets. However i need it either case in Gsheet or Excell.

    Your example does not work

     


    Regards

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Nov 28, 2022

      PedroPascual 

      To exclude the letter x it needs to be in double quotes

      = LOOKUP(1,1/(range<>"")/(range<>"x"),range)

      Otherwise 'x' would need to be a defined name holding the value or string you wish to exclude.  Another formula that I might use is

      = XLOOKUP(TRUE,ISNUMBER(range),range,,,-1)

      I think Sheets has the recent Excel 365 functions but you would know that better than I.

       

       

Resources