Lookup a Column based on Unique ID and last transaction (date)

[smalik]

I posted this earlier but I can't find it under my posts. Therefore, please forgive me if this is a duplicate request.

I am trying to fill in the Address field from a transaction file. A unique ID can have multiple transactions. Each transaction comes with an Address. I need to pull the address from the latest transaction based on the date. In case the address is missing from the last transaction, can I pull the data from the transaction before? Obviously, if the ID in Column A does not find any transaction record, I need the cell to stay blank.

Looking to complete Column B using data in Columns D through F. The results I need are highlighted in tan color.

PS: I downloaded the Xl2BB add-in but MS Excel blocked the macro saying that the source could not be verified. Did I miss a step?

Thanks for your help in advance.

 

[DanteAmor]

How about:

In cell B3:

=LET(d,D3:D17,e,E3:E17,f,F3:F17,BYROW(A3:A6,LAMBDA(br,FILTER(f,(d=br)*(e=MAX((d=br)*(f<>"")*(e))),""))))

 

[smalik]

How about:

In cell B3:

=LET(d,D3:D17,e,E3:E17,f,F3:F17,BYROW(A3:A6,LAMBDA(br,FILTER(f,(d=br)*(e=MAX((d=br)*(f<>"")*(e))),""))))

This is awesome.... Thank you.

 

[smalik]

@DanteAmor

The above formula works very well for small range. However, I am getting into a range limitation issue.

Here is the range for my data set:
Columns D, E, & F the data goes down to row 62,129
Column A goes down to row 17,654

The formula works until certain rows. After that I get an error message:
=LET(d,D3:D62000,e,E3:E62000,f,F3:F62000,BYROW(A3:A133,LAMBDA(br,FILTER(f,(d=br)*(e=MAX((d=br)*(f<>"")*(e))),""))))
This formula (range) gives me no error and provides the results I am looking for

Anything after 62,000 I get the #n/a error
=LET(d,D3:D62050,e,E3:E62050,f,F3:F62050,BYROW(A3:A135,LAMBDA(br,FILTER(f,(d=br)*(e=MAX((d=br)*(f<>"")*(e))),""))))
This formula (range) gives me #N/A error

However, for column "A" range anything over 133 gives me a #CALC! error.
=LET(d,D3:D62000,e,E3:E62000,f,F3:F62000,BYROW(A3:A134,LAMBDA(br,FILTER(f,(d=br)*(e=MAX((d=br)*(f<>"")*(e))),""))))
This formula (range) gives me #CALC! error

Any idea how should I deal with his issue?

Any help is greatly appreciated.

Regards,

 

[Fluff]

How about

=BYROW(A2:A236,LAMBDA(br,TAKE(SORT(FILTER(E2:F63000,(D2:D63000=br)*(F2:F63000<>""),""),1,-1),1,-1)))

 

[smalik]

How about

=BYROW(A2:A236,LAMBDA(br,TAKE(SORT(FILTER(E2:F63000,(D2:D63000=br)*(F2:F63000<>""),""),1,-1),1,-1)))

Hello @Fluff ,

Thanks for revising the formula. However, I am getting an "#N/A" Error now:

BTW, the Column A range should go down to 17,654. I tried changing the range for Column A, but I get the either zero or "#N/A" error.

Any thoughts?

 

[Fluff]

Check that you don't have any #N/A errors in columns D, E & F

 

[smalik]

Check that you don't have any #N/A errors in columns D, E & F

There aren't any blanks or error values in these cells.

 

[smalik]

There aren't any blanks or error values in these cells.

@Fluff

Never mind... I found it... there was one #N/A record.

 

[smozgur]

@Fluff

Never mind... I found it... there was one #N/A record.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.