How to pull recent data in excel?

[FauzanOmar]

Hello,

I would like to know if there is a formula to pull data based on latest input.

Example:
If I lookup for ID 112, the data will show name Jeffrey and formula will automatically pull 2021 data.
And if I lookup for ID 113, the data will show name Mike, and formula will only pull 2020 data because there is no data in 2021.

How do i combine/create this formula using excel?



Thank you

 

[Marcilio]

FauzanOmar, Good morning

If by any chance you still haven't solved your question, maybe these formulas will help.

I don't think they're ideal, but they do the trick for now.

B12 --> =INDEX(C6:C7; CORRESP(C10; B6:B7; 0))
C12 --> =MAX(INDIRECT("D" & (5 + MATCH(C10; B6:B7; 0)) & ":J" & (5 + MATCH(C10; B6:B7; 0))))
D12 --> =VLOOKUP(C10;B6:J7;MATCH(C12;(INDIRECT("D" & (5 + MATCH(C10; B6:B7; 0)) & ":J" & (5 + MATCH(C10; B6:B7; 0))));0) +3; FALSE)
E12 --> =VLOOKUP(C10;B6:J7;MATCH(C12;(INDIRECT("D" & (5 + MATCH(C10; B6:B7; 0)) & ":J" & (5 + MATCH(C10; B6:B7; 0))));0) +4; FALSE)

Surely some colleagues will provide much better formulas.

Please let us know if this is what you wanted.

I hope it helps.
Have a nice day!

 

[bosco_yip]

Another option

In B12, enter formula and copied across right to E12:

=LOOKUP(1,0/($B$4:$J$4=B11)/(INDEX($B$6:$J$7,MATCH($C10,$B$6:$B$7,0),0)<>""),INDEX($B$6:$J$7,MATCH($C10,$B$6:$B$7,0),0))

Then,

Copy formulas in B12:E12 and paste to B16:E16