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 but i want to exclude a specific value.

 

Any Help??

 

 

Regards

Formulas and Functions

5 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Nov 25, 2022
    I believe Arrayformula is a Sheets function. Which app are you working in?
  • 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