yogi_Pull Row By Row Most Recent Previous Order Per Specification

Google Spreadsheet   Post  #2205

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-09-2017

question by Matan Arie:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Ao79a29uT3Q;context-place=forum/docs

Trouble with **shifted** VLOOKUP in ARRAYFORMULA

Hi everyone!

I'm trying to do a VLOOKUP inside a ARRAYFORMULA, where the search_key is taken from the range A2:A, the lookup takes place in a range that starts a row lower (A3:B) and the result is taken from the B column of that range. 

=ARRAYFORMULA(IF(COUNTIF(A2:A,A2:A<2,"",VLOOKUP(A2:A,A3:B,2*SIGN(ROW(A2:A)),FALSE)))

Here's a test spreadsheet with the same data:

https://docs.google.com/spreadsheets/d/1mdj66A3t4mcbLjBQavXjS2dGrgHFMpxlPALLl-PcB_U/edit?usp=sharing

The formula is in D2.

The object of the formula is to find the previous date (B) that contains the same number (A) as the number (A) in the current row.

I've done the trick with adding *SIGN(ROW(A2:A) to the column index in VLOOKUP (even though it worked the same without it).

I thought the problem had to do with the fact that the lookup range starts a row lower than the search_key range (i.e. they had a different number of rows), but even when I compensated for that by setting the search_key range to A2:A50 and the lookup range to A3:B51, I got the same results.

Any advice?

Thanks!

Matan