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?

 

[RasGhul]

Do you have a small data set you can post?

Index & match is still faster than vlookup, xlookup should be quicker but the slowing comes from indirect.

You may get some speed improvements from changing your datasets to excel tables...

 

[AustSportingPix]

Racing.zip

Compressed (zipped) Folder

1drv.ms

Most days are tables, have been for a while.

I was thinking of each month just pasting in the results

Do you have a small data set you can post?

Racing.zip

Compressed (zipped) Folder

1drv.ms

I've uploaded the 2 files into a zip folder

 

[RasGhul]

Hi AustSport,

If you could post some sample data, using XL2bb or paste sample data directly into a reply.

Unfortunately due to security reasons I cant open your zip files.

 

[AustSportingPix]

Will do

Hi AustSport,

If you could post some sample data, using XL2bb or paste sample data directly into a reply.

Unfortunately due to security reasons I cant open your zip files.

 

[AustSportingPix]

error

 

[AustSportingPix]

Rank

All Horse Ratings.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	1	STATE OF REST (IRE)	79.21	79.21	26.40	15.84	7.92	79.21
3	2	ANAMOE	79.11	64.93	67.82	51.94	25.97	79.11	59.94	64.41	56.24
4	3	VERRY ELLEEGANT (NZ)	78.12	75.05	75.25	60.04	30.02	77.29	78.12	70.34	74.47
5	4	ZAAKI (GB)	75.89	73.51	74.47	58.81	29.41	74.70	72.82	75.89	70.65								70.65
6	5	MO’UNGA (NZ)	75.27	72.20	72.09	57.76	28.88	71.82	75.27	69.18	72.54
7	6	NATURE STRIP	75.21	72.04	73.14	57.63	28.81	70.94	75.21	73.26	68.73								68.73
8	7	MASKED CRUSADER	74.85	70.77	70.77	42.46	21.23	74.85	72.22	65.23
9	8	INCENTIVISE	74.83	73.97	74.32	59.18	29.59	74.45	74.83	73.70	72.92
10	9	PROBABEEL (NZ)	74.66	72.22	73.64	57.77	28.89	72.63	74.66	73.64	67.93
11	10	NONCONFORMIST	74.56	71.24	72.21	56.99	28.49	71.51	74.56	70.56	68.32								68.32
12	11	EDUARDO	74.44	74.27	49.51	29.71	14.85	74.44	74.10
13	12	SPANISH MISSION (USA)	74.21	69.00	46.00	27.60	13.80	63.79	74.21
14	13	CASCADIAN (GB)	73.47	70.41	71.35	70.41	35.20	73.47	71.19	69.39	70.87	67.10							67.10
15	14	RIODINI (NZ)	73.45	69.37	68.79	55.49	27.75	68.41	64.51	73.45	71.10								71.10
16	15	THINK IT OVER	73.37	71.05	71.02	56.84	28.42	68.96	70.72	73.37	71.17								71.17
17	16	YOUNG WERTHER (NZ)	73.20	69.06	69.06	41.44	20.72	69.24	73.20	64.74
18	17	FLOATING ARTIST (GB)	73.13	67.99	69.22	67.99	33.99	73.13	68.39	66.12	66.29	66.01
19	18	HOMESMAN (USA)	72.72	69.33	69.92	55.46	27.73	69.58	67.47	72.72	67.55
20	19	SIR DRAGONET (IRE)	72.42	70.64	47.09	28.25	14.13	68.86	72.42
21	20	CHAPADA	71.98	68.33	69.01	54.67	27.33	69.96	71.98	65.10	66.29								66.29
22	21	SUPERSTORM	71.78	70.18	69.99	70.18	35.09	70.10	71.23	68.65	71.78	69.12							69.12
23	22	LOST AND RUNNING (NZ)	71.72	67.28	69.75	53.83	26.91	67.85	71.72	69.69	59.88
24	23	CLASSIQUE LEGEND	71.70	71.70	23.90	14.34	7.17	71.70
25	24	DALASAN	71.59	66.93	70.31	66.93	33.46	68.72	71.59	70.61	65.57	58.16							58.16
26	25	TOFANE (NZ)	71.50	69.62	69.62	41.77	20.89	67.27	70.09	71.50
27	26	TREKKING	71.43	67.42	67.42	40.45	20.23	71.43	67.70	63.14									63.14
28	27	STANDOUT	71.39	64.15	65.61	51.32	25.66	60.11	65.33	71.39	59.77								59.77
ratings

Cell Formulas
Range	Formula
C2:C28	C2	=MAX(S2:XFD2)
D2:D28	D2	=AVERAGE(S2:XFD2)
E2:E28	E2	=IFERROR(SUM(H2,I2,J2)/3,"")
F2:F28	F2	=IFERROR(SUM(H2,I2,J2,K2,L2)/5,"")
G2:G28	G2	=IFERROR(SUM(Allhorses[@[Last Start]:[10th]])/10,"")
H2:H28	H2	=LOOKUP(2,1/(S2:XFD2<>""),$S2:$XFD2)
I2:I28	I2	=IFERROR(INDEX($S2:$XFD2,AGGREGATE(14,6,(COLUMN($S2:$XFD2)-COLUMN($S2)+1)/($S2:$XFD2<>""),2)),"")
J2:J28	J2	=IFERROR(INDEX($S2:$XFD2,AGGREGATE(14,6,(COLUMN($S2:$XFD2)-COLUMN($S2)+1)/($S2:$XFD2<>""),3)),"")
K2:K28	K2	=IFERROR(INDEX($S2:$XFD2,AGGREGATE(14,6,(COLUMN($S2:$XFD2)-COLUMN($S2)+1)/($S2:$XFD2<>""),4)),"")
L2:L28	L2	=IFERROR(INDEX($S2:$XFD2,AGGREGATE(14,6,(COLUMN($S2:$XFD2)-COLUMN($S2)+1)/($S2:$XFD2<>""),5)),"")
M2:M28	M2	=IFERROR(INDEX($S2:$XFD2,AGGREGATE(14,6,(COLUMN($S2:$XFD2)-COLUMN($S2)+1)/($S2:$XFD2<>""),6)),"")
N2:N28	N2	=IFERROR(INDEX($S2:$XFD2,AGGREGATE(14,6,(COLUMN($S2:$XFD2)-COLUMN($S2)+1)/($S2:$XFD2<>""),7)),"")
O2:O28	O2	=IFERROR(INDEX($S2:$XFD2,AGGREGATE(14,6,(COLUMN($S2:$XFD2)-COLUMN($S2)+1)/($S2:$XFD2<>""),8)),"")
P2:P28	P2	=IFERROR(INDEX($S2:$XFD2,AGGREGATE(14,6,(COLUMN($S2:$XFD2)-COLUMN($S2)+1)/($S2:$XFD2<>""),9)),"")
Q2:Q28	Q2	=IFERROR(INDEX($S2:$XFD2,AGGREGATE(14,6,(COLUMN($S2:$XFD2)-COLUMN($S2)+1)/($S2:$XFD2<>""),10)),"")
A2:A28	A2	=RANK(C2,C:C,0)
S2:S28	S2	=IFERROR(VLOOKUP($B2,INDIRECT("[ratings.xlsx]"&S$1&"!$A:$D"),2,0),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B13616	Cell Value	duplicates	text	NO

 

[AustSportingPix]

15

 

[RasGhul]

Can you also post data from $S2:$XFD2

also sample data from "[ratings.xlsx]"

The IFERROR / Aggregate array wouldn't be helping with the speed of your sheet, we might be able to use a different way of doing this formula to help processing speeds.

 

[AustSportingPix]

Can you also post data from $S2:$XFD2

also sample data from "[ratings.xlsx]"

The IFERROR / Aggregate array wouldn't be helping with the speed of your sheet, we might be able to use a different way of doing this formula to help processing speeds.

This is from the sheet 20211106

Column1	Column2
ZAAKI (GB)	74.70
CASCADIAN (GB)	73.47
MO’UNGA (NZ)	71.82
NATURE STRIP	70.94
KEIAI NAUTIQUE (JPN)	70.46
PRIVATE EYE	70.28
SUPERSTORM	70.10
HUNGRY HEART	69.84
COLETTE	69.66
HOMESMAN (USA)	69.58
AGE OF CHIVALRY (NZ)	68.89
WARNING	68.88
DALASAN	68.72
CROSSHAVEN	68.68
SOUND (GER)	67.96
JUSTACANTA (NZ)	67.74
WENTWOOD (GB)	67.42
DR DRILL (GB)	66.27
HI STRANGER	66.24
ELECTRIC GIRL	65.82
SWATS THAT	65.82
RANIER	65.40
ROCHA CLOCK	65.20
SINAWANN (IRE)	65.15
MADAM ROUGE	64.75
WANDABAA	64.18
TRICKY GAL	64.16
BELLA NIPOTINA	64.04
SEPTEMBER RUN	63.91
NUDGE	63.43
YAMAZAKI	63.20
FASHCHANEL (NZ)	63.08
ZAYYDANI (NZ)	62.97
CHAILLOT	62.96
OUR INTRIGUE (NZ)	62.80
SPIRANAC	62.64
STARELLE	62.39
LUNAR FLARE	62.37
BEGOOD TOYA MOTHER	62.19
BONVICINI	61.76
NIMALEE	61.69
VANNA GIRL	61.44
RANCH HAND	61.27
ECUMENICAL	60.88
GHODELEINE	60.69
MORVADA	60.61
HEZASHOCKA (NZ)	60.58
LE LUDE	60.53
MIMI’S AWARD	60.17
SHAQUERO	60.03
SPLINTEX	59.79
BARBIE’S FOX	59.61
STREETS OF AVALON	59.19
OUR PLAYBOY	58.62
SAMIZDAT	58.32
SILENT SOVEREIGN	58.32
SEINERS EXPRESS (NZ)	57.10
CHARMS STAR (NZ)	56.71
VASSILATOR (NZ)	56.06
ARCHEDEMUS	55.21
SHIBLI (GB)	55.14
EQUATION	54.84
IT’SOURTIME	54.67
KISSINGER (NZ)	54.51
TURBEAU	54.23
ALCYONE	54.18
JAZZLAND	54.17
POLLY GREY (NZ)	54.09
ZOUSHACK	54.07
MAYFAIR SPIRIT (IRE)	54.01
LADYLOVESTOGAMBLE	53.99
MAGNAJET	53.91
LUNATIC FRINGE	53.82
BLAZE A TRAIL	53.70
ANOTHER ONE	53.53
AVION FURY	53.49
TATUNKA	53.47
BEAUFORT PARK	53.28
DUCHESS OF DORSET	53.16
BLESK (NZ)	53.09
AMICHE	53.09
SUAVE	53.04
UNDENIABLE	53.02
KEATS (IRE)	52.86
DICE ROLL (FR)	52.70
FRANCESCO GUARDI (IRE)	52.69
FOX FIGHTER	52.45
TYCOON BEC	52.38
SARACEN KNIGHT (IRE)	52.23
VIREN	52.14
LEGIONNAIRE	52.05
FLAT HEAVEN (FR)	51.79
WELSH LEGEND	51.71
KAZIO	51.65
MR CANCUN (NZ)	51.62
PORT LOUIS	51.57
BEND THE KNEE	51.47
VITRUVIUS	51.42
LA BELLA COSTA	51.39
SCHWOON	51.34
AIR TO AIR	51.30
NICTOCK	51.17
OH PLEASE DIANNA	51.15
SELEQUE	51.12
UNDER OATH	51.02
HERMAN HESSE (GB)	50.95
DIVA BELLA	50.92
KINLOCH	50.75
MEDIA STARGUEST	50.67
WAR ETERNAL (NZ)	50.64
DEPTH THAT VARIES	50.57
MAHASHAKTI	50.52
SAQUON	50.41
SUPPRESSION (NZ)	50.27
BUDHWAR	50.18
DESTINATION	50.05
PRINCESS JENNI (NZ)	50.00
DAILY BUGLE	49.89
DESIGNER MAID	49.84
BERGEN	49.84
CARDINAL GEM	49.80
SKELM	49.79
GHOSTWRITER	49.75
SO SAY YOU	49.74
LEITER	49.73
EL SALTO	49.72
SAM’S IMAGE	49.72
TRAVEST (NZ)	49.67
UPPER EAST SIDE	49.64
YULONG DEFENCE	49.62
KAMIEN	49.62
BOWERY BREEZE	49.62
CALGARY QUEEN	49.59
HAWKER HURRICANE	49.49
MEDIEVAL MISS	49.38
SIZZLING CAT	49.19
THE LAST NAPOLEON	49.16
JOHANDOUGH	49.12
PROMPT PRODIGY (NZ)	48.68
SCHILLER’S MYST	48.59
SNOWFIRE	48.55
FORTIFIED (NZ)	48.50
APRES LA MER	48.49
FLORIDA DREAM	48.47
JIMMY THE BEAR	48.41
TYCHE GODDESS	48.35
RUSSBUSS	48.23
PARACHUTER	47.93
PRIMITIVO	47.90
DECENT RAINE	47.74
POINT COUNTERPOINT	47.71
MERLINITE	47.52
LANDO BAY	47.41
OUR BAMBINO	47.41
OLYMPIC GAZE	47.40
CAPE BRETON	47.39
HASSELTOFF	47.28
TERWILLIKER	47.25
ARABOLINI (GER)	47.12
LAVISH GIRL	46.96
TOOSTAR	46.65
KAONIC (NZ)	46.54
MIKADO	46.43
O’PRESIDENT	46.34
PANDORA BLUE	46.31
CHAMPAGNE VEGA	46.18
CHASE MY CROWN	46.14
APACHE RED	45.96
LUCKY BANNER	45.51
SOOBOOMA	45.40
DEPARTING BULLET	45.30
OUR HEIDI	45.16
KERIOTH	45.10
MY PRINCE CHARMING	44.91
BOOM BOOM SWEET (NZ)	44.87
MICRO	44.66
RED HOT CHILLYPINS (NZ)	44.58
BRANDERS RULE	44.55
AEROSPEED	44.48
I AM THE FOX	44.47
SNITWAY	44.31
VOLCANIC ROCK	44.23
TELEKINESIS	44.23
MAUSER	44.11
TYMPANIST (NZ)	44.11
BELLOWS	44.09
DIAMONDS AND RUST	43.95
STATESVILLE	43.91
SKY DIAMONDS	43.82
TRUE METTLE	43.78
SMYTZER	43.54
DANGERS	43.40
NO NEW TRICKS	43.03
STREET BABY	42.73
ALBANIAN EAGLE	42.61
RESOLUTIONS	42.60
MIGHTY FEAT (NZ)	42.45
ZAZSTER	42.39
HOSIER (IRE)	42.25
EL MO	42.13
EFFORT	41.89
INTERNATIONAL GEM	41.81
FAST MELODY	41.75
OUR BOY BRYAN	41.66
SOCIALITE MISS	41.55
EASTERN PEGASUS	41.46
ART DEALER (NZ)	41.44
AZOUSTIC	41.01
CHICH’S SPIRIT	40.92
BISMARC	40.78
POST FREE	40.71
WHOWOODATHORT	40.63
ECLAIR RHYTHM (NZ)	40.47
FALTONIUS	40.23
STEEL THE MOMENT	40.09
CHESTNUT SCAFFA	40.01
ANDORRA LA VELLA	39.90
NAOMI	39.56
OCEAN DRIVE	38.15
STERN IN GRINZING	38.05
SPHERA	38.02
ROYAL TYKE	37.94
ROCKET OWEN	37.65
CLEVER GEORGE	37.56
YELLILONG BUGG	37.45
ARISTOBEAUX	37.18
RECIDIVIST	37.17
THE YACHTSMAN	36.96
FULL AHEAD	36.62
OLYMPIC SPIRIT	36.58
SHEZASHOOTINGSTAR	35.06
MAINCOURT	23.88
CHESTORIUS (NZ)	23.85
SEJARDAN	23.82
SHALATIN	23.66
OJAI	23.40
PHERE THE EX	23.37
GUARDIAN SPIRIT	23.34
CANNONBALL	23.21
FIORENTE SPRITZ	22.85
WOLF SKILL	22.82
REDSKIN JIMMY (NZ)	22.80
HAILEY’S DIVA (NZ)	22.74
SNITCAT	22.50
WAMMO	22.49
CONTEMPORARY	22.47
SACHEM	22.20
MAFIORE	22.07
THE PROBLEM WITH	22.01
NO BABY NO	22.01
HOOROOROB	21.89
VIKANDER	21.85
NOBEL	21.56
RUBY MOCKINGBIRD	21.47
SHIRVO	21.43
RANGI TANE (NZ)	20.99
DENIMS	20.67
KANNENBERGS ROAD	20.63
BIRDONAWING	20.63
JEDAN	20.59
BOWRIDER	20.55
JALASAAT	20.50
ESCENTUAL	20.45
MOFEED WARRIOR	20.42
TOM ROCKS	20.41
WANDA’S LEGACY	19.76
MOUNT BRILLIANT	19.58
OUR BEST PAL	18.92
AESOP’S FABLE	0.00
KITZBUHEL (USA)	-0.14
REAL TIME WARRIOR	-0.14
DREAM RUNNER	-0.27
MAHSINGER	-0.27
DEFY	-0.34
INVINCIBLE DASH	-0.34
KERMATIN	-0.34
NOTABADIDEA (NZ)	-0.34
BIRDONAWINNINGPOST	-0.48
PROVEN CLASS	-0.48
FIVE KINGDOM	-0.54
GROUP THINK	-0.54
NEMINGAH (NZ)	-0.54
THIS IS SO	-0.54
WICHITA WARRIOR	-0.54
ZAKERIZ	-0.54
ROCKAMO (NZ)	-0.75