Nov 25 2022 08:29 AM
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
Nov 25 2022 09:29 AM
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)
Nov 25 2022 09:51 AM
Nov 26 2022 08:20 AM
More exactly it appears if convert Sheets file to Excel.
Nov 28 2022 12:24 AM
@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
Regards
Nov 28 2022 03:13 AM
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.