Need a faster way to look up

[AustSportingPix]

Hey all
I have a database that is updated & grows everyday. At the moment I have 13000+ horses, each one has more than 150 vlookups with indirect for another workbook.
So as you can imagine, adding new data takes its sweetass time, at the moment is around 10 minutes, then 5 minutes to remove the doubles, then a few minutes to sort.

So I'm wondering if there's another way.

This is the formula I'm using: =IFERROR(VLOOKUP($B2,INDIRECT("[ratings.xlsx]"&A$1&"!$A:$B"),2,0),"")

I've heard hlookup is quicker, I've heard indexmatch is quicker but cannot work them out.

Any ideas?

 

[Fluff]

Two things that instantly spring to mind.
1) does your data really go all the way to column XFD? If not limit the ranges in the formulae.
2) Never use whole column references, which you are doing in your vlookup. You would be better of using something like

=IFERROR(VLOOKUP($B3,INDIRECT("[ratings.xlsx]"&S$1&"!$A$2:$B$1000"),2,0),"")

 

[AustSportingPix]

Two things that instantly spring to mind.
1) does your data really go all the way to column XFD? If not limit the ranges in the formulae.
2) Never use whole column references, which you are doing in your vlookup. You would be better of using something like

=IFERROR(VLOOKUP($B3,INDIRECT("[ratings.xlsx]"&S$1&"!$A$2:$B$1000"),2,0),"")

Not yet, but will get there and filling up fast.

 

[Fluff]

Does limiting the range of the vlookup help?

 

[AustSportingPix]

Does limiting the range of the vlookup help?

Seems to be a touch quicker

 

[Fluff]

The biggest problem is the Indirect & the sheer amount of data especially if you have columns going out to AAA and beyond. However you could try these & see if they are faster.

=IFERROR(INDEX(INDIRECT("[ratings.xlsx]"&S$1&"!$B$2:$B$1000"),MATCH($B2,INDIRECT("[ratings.xlsx]"&S$1&"!$A$2:$a$1000"),0)),"")

=XLOOKUP($B2,INDIRECT("[ratings.xlsx]"&S$1&"!$a$2:$a$1000"),INDIRECT("[ratings.xlsx]"&S$1&"!$b$2:$b$1000"),"",0)

 

[AustSportingPix]

The biggest problem is the Indirect & the sheer amount of data especially if you have columns going out to AAA and beyond. However you could try these & see if they are faster.

=IFERROR(INDEX(INDIRECT("[ratings.xlsx]"&S$1&"!$B$2:$B$1000"),MATCH($B2,INDIRECT("[ratings.xlsx]"&S$1&"!$A$2:$a$1000"),0)),"")

=XLOOKUP($B2,INDIRECT("[ratings.xlsx]"&S$1&"!$a$2:$a$1000"),INDIRECT("[ratings.xlsx]"&S$1&"!$b$2:$b$1000"),"",0)

Thanks so much, seems much much quicker

I was watching a few youtube videos and all were saying Vlookup is slow but I couldn't work out index/match or even xlookup.

 

[Fluff]

Glad we could help & thanks for the feedback.