How do I modify this formula to work with information from cells 'AT4,AX4,BB4,BF4,BJ4,BN4' instead of the range 'AT4:BN4'

=lookup(1,ArrayFormula(1/(AT4:BN4<>"")),AT4:BN4)

In this linked spreadsheet, I only want the last exit price to automatically show in cell BS4, not the last cell value in the entire range. google sheet spreadsheet example

I would appreciate any help.

CodePudding user response：

I've added a new sheet ("Erik Help"). It's a duplicate of your original sample sheet.

Since you are really wanting to find the last "Price" per row for any number of rows, I used an array formula in BS4 that will accomplish this:

=ArrayFormula(IF(AS4:AS="",,1*REGEXEXTRACT(TRANSPOSE(QUERY(TRANSPOSE(IF((RIGHT(AT$3:BN$3,5)<>"Price") (AT4:BN=""),,"~"&AT4:BN)),,COLUMNS(AT:BN))),"~([^~] )$")))

Essentially, this will smash together each existing "Price" in a row (as determined by the word "Price" at the end of Row 3) prefaced by a tilde, forming one string, e.g.,

~9.5 ~11.5 ~13.5 ~10.65

That smash is accomplished with this:

TRANSPOSE(QUERY(TRANSPOSE([row information here]),,COLUMNS(AT:BN)))

Then REGEXEXTRACT will find the last tilde in the list and extract whatever is after it. The 1* will convert that to a number.