JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 785
- Office Version
- 365
- Platform
- Windows
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
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,
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
VBA Code:
=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,