0

I made myself a task a bit more difficult than I had to but I think it is doable.

=VLOOKUP( INDIRECT("A" & ROW()) ;Activations!A2:F ; 3; FALSE)

This code is working for taking up ID value specified in A+ROW() in current sheet and looking for it in the specified range in Activations sheet. It prints the associated result for the 3rd column.

The thing is that with the end argument of FALSE or TRUE it sorts already sorted data (in coding terms it is just reversing the array of data). If you append data, the data becomes unsorted (unless you manually trigger sorting), and thus the VLOOKUP prints the wrong element. I see that people are using the MAX() inside VLOOKUP but this cannot be applied in this scenario for the Activations sheet range to be sorted..

I tried XLOOKUP but this is too difficult without any success.

You can test/see here https://docs.google.com/spreadsheets/d/19-k1taC8kIeRQ2Dg2j0lDqbgJ_AAbaYrT0xN2jFaUSc/edit?usp=sharing

Help?

Thanks

Svetoslav
  • 31
  • 2
  • 6

1 Answers1

1

You may try:

=vlookup(A5;sort(Activations!A:C;2;0);3;)

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • You guys definitely know Excel in here LOL. Yea I need that INDIRECT thing for the project so no need of A5 explicitly. Anyway it works ! Thanks – Svetoslav Apr 26 '23 at 14:29
  • can you please tell what does the sort arguments ;2;0 do ? Because in my project I get some kind of numbers displayed as result but not text or date.. – Svetoslav Apr 26 '23 at 14:38
  • it sorts the whole range of `A:C` taking into account that the second column (that would be `date`)is now in descending order – rockinfreakshow Apr 26 '23 at 14:41
  • Please look at the example again as I replicated what's in my production sheet. Strange numbers displayed instead the date, I'll mark as solved as soon this is resolved. – Svetoslav Apr 26 '23 at 14:50
  • This guy helped me a lot in the sheets ! Thanks again ! – Svetoslav Apr 26 '23 at 15:07