Is there a smart way to: XLOOKUP. If value A is duplicate show next value

[MRDBS]

Hey

Really sorry if my question has an obvious answer, I blame it on it being really early and my logic engine not yet being fired up.

I am trying to look up next value in an array, if the lookup value is a duplicate to the one above.

I am currently using XLOOKUP to find values in another sheet.
=XLOOKUP(A;B:B;C:C;0) (Simplified)

Now I would like to add an if function,
=XLOOKUP(A2;B:B;If(A2;A1;(C:C)+1;C:C);0)
I also tried:
=IF(A2;A1;(A;B:B;(C:C)+1;(A;B:B;C:C;0)))

I am not sure (C:C)+1 works, do you have an alternative suggestion.

Thank you!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[MRDBS]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Ahh sorry I will update that right away.
I am using Excel/Office 365

 

[Fluff]

Thanks for that.
How about

=LET(f,FILTER(Sheet1!$B$2:$B$1000,Sheet1!$A$2:$A$1000=A2), IF(A2=A1,INDEX(f,1),INDEX(f,2)))

 

[MRDBS]

Thanks for that.
How about

=LET(f,FILTER(Sheet1!$B$2:$B$1000,Sheet1!$A$2:$A$1000=A2), IF(A2=A1,INDEX(f,1),INDEX(f,2)))

Thank you will try that, I never worked with LET.

 

[MRDBS]

Thanks for that.
How about

=LET(f,FILTER(Sheet1!$B$2:$B$1000,Sheet1!$A$2:$A$1000=A2), IF(A2=A1,INDEX(f,1),INDEX(f,2)))

I tried your formular, but it returns cell B from my example not C.
I then changed your formular from Sheet1!$B$2:$B$1000,Sheet1!$A$2:$A$1000 to Sheet1!$C$2:$C$1000,Sheet1!$B$2:$B$1000, which worked, but when I draw it down to the next rows, then it shows #ref error.

 

[Fluff]

In that case can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MRDBS]

In that case can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you Fluff, will try that!

 

[Fluff]

Ok

 

[MRDBS]

Ok

I now got it to work, I encapsulated your LET formular with and IF and my XLOOKUP as alternative.

SO:
=if(A2=A1;LET;XLOOKUP;0) and it works but I needed to adjust INDEX(f,1),INDEX(f,2 to: INDEX(f,2),INDEX(f,1), to show the the next and not first value.!

As mentioned, I never tried LET before, but surely will do some research to see what else I can do with it.

Thank you again!