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.

1

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)))
}
2

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.