1

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.

0

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)))
}
  • Perfect thanks alot - i have totaly forgot the sort possibilitie. Thanks allot. Will try now everythink :D – Borut MyStur Oct 22 '19 at 7:11
  • Don't forget to mark the question as resolved or complete your question with additionnal info ! Have a nice day. – pjmg Oct 22 '19 at 7:59
  • How and where to do that? Cant find a button that the question is answered – Borut MyStur Nov 20 '19 at 9:29
  • stackoverflow.com/help/someone-answers – pjmg Nov 20 '19 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.