Any better alternative to this Indirect Formula

[JumboCactuar]

Hi,
i have the following formula which i write to a full column using VBA.
And this formula ive pinpointed as the source to slowing everything down - due to Volitile formula i think

=INDIRECT("Data!D"&ROW(XLOOKUP(A2,Data!A:A,Data!D:D,""))+1),"")

Aim:
In Data Sheet Column A = Name Lookup
In Data Sheet Colum D = Prices sorted Low to High

Im trying to get the 2nd lowest price for the Name in current Row
Formula above does an xlookup to find the first ROW then adds 1

e.g
current sheet A2 = John45

xlookup finds John45 in row 71 of Data Sheet
so 2nd lowest price is in Data!D72

Any better way ?

appreciate any help,

 

[Fluff]

Maybe

=INDEX(Data!D:D,XMATCH(A2,Data!A1:A1000,0)+1)

 

[JumboCactuar]

Much faster thanks @Fluff

This formula in post1 is what caused this to run slow

Getting position of an item in a list

Hi, sorry title doesn't really explain it well: I have data like below (100k + rows) Item CodeSellerPrice FR11ABCTech16.49 FR11KLG15.19 FR11JustPro15.11 FR11MapLtd14.99 GR44KLG38.99 GR44ABCTech36.49 GR44JustPro35.99 ZD16KLG12.99 ZD16JustPro12.89 ZD16ABCTech12.49 PG20JustPro22.79...

www.mrexcel.com

 

[Fluff]

You're welcome & thanks for the feedback.