shorten very long formula

[dioxide]

hi all, i currently have this formula in a worksheet of mine, and although it works correctly very well, as you can see its very long, i wondered if there is any way i can shorten down the size of it.
i have used concatenate but im sure there is another better way of doing this

=CONCATENATE(IF(B132="Arsenal",VLOOKUP(C133,'div1'!$X$3:$Y$67,2,FALSE), IF(B132="Blackpool",VLOOKUP(C133,'div1'!$X$70:$Y$134,2,FALSE), IF(B132="Burnley",VLOOKUP(C133,'div1'!$X$137:$Y$201,2,FALSE), IF(B132="Chelsea",VLOOKUP(C133,'div1'!$X$204:$Y$268,2,FALSE), IF(B132="Coventry",VLOOKUP(C133,'div1'!$X$271:$Y$335,2,FALSE), IF(B132="Crystal Palace",VLOOKUP(C133,'div1'!$X$338:$Y$402,2,FALSE), IF(B132="Derby",VLOOKUP(C133,'div1'!$X$405:$Y$469,2,FALSE), IF(B132="Everton",VLOOKUP(C133,'div1'!$X$472:$Y$536,2,FALSE), IF(B132="Huddersfield",VLOOKUP(C133,'div1'!$X$539:$Y$603,2,FALSE), IF(B132="Ipswich",VLOOKUP(C133,'div1'!$X$606:$Y$670,2,FALSE), IF(B132="Leeds",VLOOKUP(C133,'div1'!$X$673:$Y$737,2,FALSE), IF(B132="Liverpool",VLOOKUP(C133,'div1'!$X$740:$Y$804,2,FALSE), IF(B132="Man City",VLOOKUP(C133,'div1'!$X$807:$Y$871,2,FALSE), IF(B132="Man United",VLOOKUP(C133,'div1'!$X$874:$Y$938,2,FALSE), IF(B132="Newcastle",VLOOKUP(C133,'div1'!$X$941:$Y$1005,2,FALSE), IF(B132="Nottm Forest",VLOOKUP(C133,'div1'!$X$1008:$Y$1072,2,FALSE), IF(B132="Southampton",VLOOKUP(C133,'div1'!$X$1075:$Y$1139,2,FALSE), IF(B132="Stoke",VLOOKUP(C133,'div1'!$X$1142:$Y$1206,2,FALSE), IF(B132="Tottenham",VLOOKUP(C133,'div1'!$X$1209:$Y$1273,2,FALSE), IF(B132="West Brom",VLOOKUP(C133,'div1'!$X$1276:$Y$1340,2,FALSE), IF(B132="West Ham",VLOOKUP(C133,'div1'!$X$1343:$Y$1407,2,FALSE), IF(B132="Wolves",VLOOKUP(C133,'div1'!$X$1410:$Y$1474,2,FALSE), )))))))))))))))))))))))

[BadlySpelledBuoy]

Hi and welcome.

Might be easier to help if we can see the file. Could you attach it?

Click on "Go Advanced" below your reply then scroll down to "Manage Attachments" to open the upload window.

BSB

[jason.b75]

With a list of the teams that are used in your formula somewhere in the sheet, you could shorten it to.

Please Login or Register  to view this content.

This is assuming that the number of rows remains consistent for all teams listed, I only checked the first 5.

[Jacc]

I would probably do something like this (see attachment).
I'm using the Excel Table feature. A dropdown menu with the teams and below that a formula that builds an Excel Table reference from the team name.
I then use that reference with INDIRECT in a VLOOKUP.
A bit cumbersome to set up (yup, that's me alright) but once set up it is pretty tidy.
I didn't complete the table, too much work. To complete it, select a column, hit Ctrl + +, copy the header name from the left on to the new column (it will be the same name but with a 2 in the end).

[dioxide]

With a list of the teams that are used in your formula somewhere in the sheet, you could shorten it to.

Please Login or Register  to view this content.

This is assuming that the number of rows remains consistent for all teams listed, I only checked the first 5.

ive just tried using this and adjusting the code to suit the cell i tried it on, and it works perfectly, my questions would be this could be adjusted to suit any amount of teams as long as they are added to the list and follow the same size of 67? [i currently have just 22 teams on it, but i would like to add others] and I could adjust the 67 to whatever i want as well i presume? if i wated only 42 say, i would adjust the numbers at the end to suit? my other question is i understand most of the formula, but could you break it down and explain in dummies english for me how the code works? sorry my excel knowledge is basic hence my question on here

I have attached a file with images to let you see what I mean.

[jason.b75]

As long as the pattern remains consistent, it would work for any number of teams with any number of rows.

This bit of the formula is used to create the range from one of your original vlookup formulas, OFFSET('Div1'!$X$3,(MATCH(B132,List!$A$1:$A$20,0)-1)*67,0,65,2) so breaking it down:-
OFFSET('Div1'!$X$3, the first vlookup started in X3, so this is the base point where the offset needs to start.
(MATCH(B132, the critera from your repeated IF logical tests.
List!$A$1:$A$20 a list to compare the criteria to.
,0) 0 is the same as FALSE in vlookup or match (exact match to criteria).
-1) an adjustment to the result of MATCH, for the formula to work, the first result needs to be 0, the second needs to be 1, etc. MATCH will return 1 and 2, so subtracting 1 gives what we need.
*67 number of rows between the starting point of each vlookup range. X3 to X70 = 67 rows.
,0 the formula is already set to column X, so no change needed hence 0 is used here.
,65,2) the number of rows and columns in each lookup range, X3:Y67 = 65 rows and 2 columns.

Hopefully that will make sense.