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.
>Solution :
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.