Google-sheets – Google sheets Xlookup dont work but need results in table the max date on the condition of userlist

google sheetsgoogle-sheets-arraysvlookup

Need some help with some data processing.
Link of the sheet

Example – I need on :

Page-2-Seznam članov > Coloumn C : It needs to be the max dates.
The function need to search on the liste the name from Page-2-Seznam članov > Coloumn B and in area Page-3-Podaljsave to give the outpoot from the same person the exact max date from Page-3-Podaljsave > Coloumn E

Page-2-Seznam članov > Coloumn D : It needs to be the max dates.
The function need to search on the liste the name from Page-2-Seznam članov > Coloumn B and in area Page-3-Podaljsave to give the outpoot from the same person the exact max date from Page-3-Podaljsave > Column F

I tried functions like filter like match etc, but i cant get any result.
Can Someone help?

Dont know about Query, but I know if I input a =Query then the whole opening of the document takes a long time.

Best Answer

Problem 1 :

Page-2-Seznam članov > Coloumn C : It needs to be the max dates. The function need to search on the list the name from Page-2-Seznam članov > Coloumn B and in area Page-3-Podaljsave to give the outpoot from the same person the exact max date from Page-3-Podaljsave > Coloumn E

In Page 2 > column C > cell C3 you can use vlookup.

=VLOOKUP(B3,'Page-3-Podaljsave'!$B:$E,4,false)

A =sort function to guarantee that the output will be the max date available. 

=VLOOKUP(B3,sort('Page-3-Podaljsave'!$B:$E,4,false),4,false)

Then use an arrayformula to cover the range. 

=arrayformula(VLOOKUP(B:B10,sort('Page-3-Podaljsave'!$B:$E,4,false),4,false))

Be careful, the bigger the range in this lookup, the slower the sheet will be to open/process new data.

Problem 2 :

Page-2-Seznam članov > Coloumn D : It needs to be the max dates.
The function need to search on the liste the name from Page-2-Seznam članov > Coloumn B and in area Page-3-Podaljsave to give the outpoot from the same person the exact max date from Page-3-Podaljsave > Column F

Same idea :

=arrayformula(VLOOKUP(B3:B10,sort('Page-3-Podaljsave'!$B:$F,5,false),5,false))

You can combine the two formulas in a single array with this in C3 : 

={arrayformula(iferror(VLOOKUP(B3:B10,sort('Page-3-Podaljsave'!$B:$E,4,false),4,false))),arrayformula(iferror(VLOOKUP(B3:B10,sort('Page-3-Podaljsave'!$B:$F,5,false),5,false)))}

Bonus : you can reduce 'Page-3-Podaljsave'!$B:$E to an array with only two columns : {'Page-3-Podaljsave'!$B:$B,'Page-3-Podaljsave'!$E:$E} then use it in the function like this :

={
arrayformula(iferror(
  VLOOKUP(B3:B10,
    sort({'Page-3-Podaljsave'!$B:$B,'Page-3-Podaljsave'!$E:$E},2,false),
  2,false))),
arrayformula(iferror(
  VLOOKUP(B3:B10,
    sort({'Page-3-Podaljsave'!$B:$B,'Page-3-Podaljsave'!$E:$E},2,false),
  2,false)))
}
Related Topic