• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to pull recent data in excel?

FauzanOmar

New Member
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?

81772

Thank you
 

Attachments

  • Recent Value Pull.xlsx
    9.4 KB · Views: 7
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!
 

Attachments

  • NOV-22-2022-Chandoo-Recent Value Pull.xlsx
    11.1 KB · Views: 1
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

81791
 

Attachments

  • Recent Value Pull (BY).xlsx
    11.7 KB · Views: 2
Last edited:
Back
Top