Mastering Excel's XLOOKUP Function

[@tajulislam4294]

i have a question. If A has more than 2 data. And I need the first one, then second one and then the last one. What would be the formula.

[@indzara]

You can achieve it using INDEX and SMALL function.
=INDEX(B2:B8,SMALL(IF(A2:A8=D1,ROW(A2:A8)-ROW(A1)),D3:D5))
B2:B8 is the return Value.
A2:A8 is has the repeating data
A1 is the initial row before the data (Header)
D3:D5 are look up values 1st, 2nd and last. I had (1 in D3, 2 in D4 and 4 in D5) With a sample of 4 duplicating data.
Best wishes.