Forum Discussion
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...
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
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.