GRcade

Joer wrote:I feel bad that I was able to do what I need with the XLOOKUP after how much time you spent doing your example.

Thanks everyone for your help.

Another question off the back of this...



I've put the same formula in two cells. The one I was testing to get it working which works, and the other where I actually want it to appear which doesn't. (You can click on the image to enlarge it)

Is there something obvious I'm missing about why this would happen?

Joer wrote:I feel bad that I was able to do what I need with the XLOOKUP after how much time you spent doing your example.

Thanks everyone for your help.

Another question off the back of this...



I've put the same formula in two cells. The one I was testing to get it working which works, and the other where I actually want it to appear which doesn't. (You can click on the image to enlarge it)

Is there something obvious I'm missing about why this would happen?

Joer wrote:I feel bad that I was able to do what I need with the XLOOKUP after how much time you spent doing your example.

Thanks everyone for your help.

Moggy wrote:I am strawberry floating useless with Excel.

I have a huge list of people and need to find the latest salary for each one. But none of them have the same dates. Example:

MembNo: 345 Mr A Jones 01/01/2020 £23,432.53
MembNo: 345 Mr A Jones 01/01/2021 £24,356.42
MembNo: 345 Mr A Jones 05/08/2022 £25,432.32
MembNo: 289 Mr B Smith 01/02/2021 £78,532.43
MembNo: 289 Mr B Smith 04/09/2021 £79,533.57

Is there a simple way to get Excel to highlight the most recent date for each unique member number?

Cuttooth wrote:Can you just custom sort the table by name then date, newest to oldest?

EDIT - You can then remove duplicate names and the first instance in the table will be kept.

Probably better to go by member ID actually.

Cuttooth wrote:If you want to keep the data intact you can also used the tried and true VLOOKUP after sorting it as that only ever returns the first match it comes across.

There'll be other ways too of course.

Joer wrote:Anyone know an easy way to do this?

twitter.com/alpacaaurelius/status/1624832049738547200

Joer wrote:Rarely leading zeroes. It may just be those 2 in the list of 200 odd to be honest.

Balladeer wrote:

Joer wrote:Rarely leading zeroes. It may just be those 2 in the list of 200 odd to be honest.

Not that it's hard to add an exception anyway, but I've just realised I'm not doing what you want regardless. Have C2 read =left(B2,8), then have D2 read:
=iferror(index(B:B, match(A2, C:C, 0), 1), "")
Then drag both down. It won't group them neatly up near the top, you'll have to do a manual filter for that or get jiggy with Visual Basic, but it'll give you a list of the matched codes from column B and empty spaces where no matches exist.