Access other sheet's data from inside of ARRAYFORMULA

Question

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.

Example

Answer 1

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)