Google Sheet Lookup With Arrayformula

1
votes

I am using below formula to lookup the last value of the matching string. The formula is working fine but is giving result for only one cell i.e. not working with ARRAYFORMULA:

=ArrayFormula(if(isblank(D1),"",IFERROR(ArrayFormula(LOOKUP(2,1/(A:A=D1),B:B)))))

Below is the link for the sample sheet:

https://docs.google.com/spreadsheets/d/1pcW0BUDEG7OGc1oh2b_4bTtZ2pB-hSmGCoTj5wnkYNo/edit#gid=2002876038

Any help on above will be greatly appreciated.

Regards

1
google-sheetsgoogle-sheets-formula
Try this formula =ARRAYFORMULA (IF (D:D<>"", VLOOKUP (D:D, sort({A:A, row(A:A)}, 2,false), 2,false ), )) – Sergey

1 Answers

0
votes

use:

=ARRAY_CONSTRAIN(SORTN(SORT({A:B, ROW(A:A)}, 3, ), 9^9, 2, 1, 1), 9^9, 2)

enter image description here