How to exclude a Value from a lookup & array formula

New Contributor

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

5 Replies

@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)
I believe Arrayformula is a Sheets function. Which app are you working in?

@Patrick2788 

More exactly it appears if convert Sheets file to Excel.

@Peter Bartholomew 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

PedroPascual_0-1669623870997.png

 


Regards

@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.