Copy value from another sheet based on date

Occasional Visitor

I have two sheets in a file, one is current, the other one is list of all historical data entered before, containing date/name/amount/price....etc. The same item could have multiple dates on the historical date sheet.

How do  I set it up so that when I enter partial item name in the current cell on current sheet, it will auto-find the value on the other sheet, with latest date on the other sheet?

1 Reply

@XepherZenith 

=LARGE(IF(NOT(ISBLANK(B3))*ISNUMBER(SEARCH(current!B3,historical!$C$3:$C$26)),historical!$B$3:$B$26),1)

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

current.JPG

historical.JPG