Access other sheet's data from inside of ARRAYFORMULA
I am extracting data from other sheet and I'd like to use ARRAYFORMULA.
In sheet1, I have a sales revenue of different sites on different dates and it's been sorted by sites and dates.
I want to retrieve every site's last day's revenue.
On sheet2, I listed every site and is able to get row# of site's last day using the MATCH function. (Cloumn B)
But I can't get revenue data with such row number.
The formula I used in sheet2 Column C
=ARRAYFORMULA(IF(A2:A<>"","'sheet1'!"&ADDRESS((B2:B),2),""))
It's the best result I can get without error. It seems it's close to answer but I am stuck.
Result of sheet2
| Column A | Column B | Column C. |
|---|---|---|
| Site 1 | 2 | 'sheet1'!$B$2 |
| Site 2 | 7 | 'sheet1'!$B$7 |
I know INDEX should solve this but it cannot be used in ARRAYFORMULA.
If anyone can help on this it will be appreciated.
Since the dates are already sorted; you can go with:
=arrayformula(xlookup(A2:A9,sheet1!A:A,sheet1!C:C,,,-1))
Alternative to bring in location & revenue in one go:
=choosecols(sortn(sort(sheet1!A2:C,2,),9^9,2,1,1),1,3)
