Search code examples
google-sheetsvlookupxlookup

Google Sheets VLOOKUP and MAX to sort the end result

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

Solution

  • You may try:

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

    enter image description here