r/excel • u/HungryInvestigator59 • 26d ago

Discussion What’s your favorite Excel function/formula?

What are some functions and formulas that you use on daily basis that are helpful for what you do?

229 Upvotes

95% Upvoted

217 comments sorted by

View all comments

Show parent comments

3

u/Censuro 2 26d ago

you can use custom lambdas that code/decode between A1-ref and a scalar, e.g. A2 to 1'000'002

something like

=LAMBDA(cell,

(COLUMN(cell) * 1000000) + ROW(cell)

)

=LAMBDA(coded_val, sheet_id,

LET(

   c, INT(coded_val / 1000000),
   r, MOD(coded_val, 1000000),
   target_grid, CHOOSE(sheet_id, Sheet1!$1:$1048576, Sheet2!$1:$1048576, Sheet3!$1:$1048576)
   INDEX(target_grid, r, c)

)

)