Google-sheets – How to select a particular cell from an ‘array formula’ result

google sheets

Take this formula:

=GOOGLEFINANCE("PG", "high", DATE(2014, 4, 1))

This formula returns an 'array result', meaning it generates a little table over multiple cells, like this:

The top-left cell pictured ('Date') is the one I wrote the formula in; the actual value I want is written to a cell below-right of it.

Is there some way I can wrap the formula with another formula, so I can just grab the exact cell that I want? I.e. so the cell I write my formula in will just contain "80.84", and no other nearby cells get overwritten. Something like:

=GIVEMEONECELLFROMTHIS(GOOGLEFINANCE("PG", "high", DATE(2014, 4, 1), 'just the one at the bottom right pls')

Best Answer

Please try:

=index(GOOGLEFINANCE("PG", "high", DATE(2014, 4, 1)),2,2)

Related Solutions

Google-sheets – Return a blank when cell reference is blank

Google Sheets as well as other spreadsheet application handles dates as serialized number. The large number that you refer that is displayed when a cell on AL column is blank is very likely that it is the number that represents the date on the corresponding cell on column G.

Regarding returning a blank, try something like this:

=ArrayFormula(IF(ISBLANK(A:A),,"Non blank"))

Please note that the second argument of the IF() function is left empty (,,): IFS(), IF() and other similar functions return blank when the corresponding argument is empty.

Google-sheets – How to check if a cell is blank for Conditional formatting

With the range set to: B3:O1000

Try:

=AND(ISBLANK(B3),INT($A3)=INT(today()))

or even shorter:

=AND(ISBLANK(B3),$A3=today())

Best Answer

Related Solutions

Google-sheets – Return a blank when cell reference is blank

Google-sheets – How to check if a cell is blank for Conditional formatting

Related Topic