Shorten very long vlookup formula

[pframpton]

I have a spreadsheet I use to calculate the salary cap for our fantasy hockey league and I use a very long VLOOKUP formula to calculate it, but it looks very ugly. Is there a way to shorten it, or use another Excel matrix formula (INDEX MATCH)?

I am not 100% in and while the formula does the work it just is a bit too unwieldy so any help would be appreciated.

This is what it looks like:

=(((VLOOKUP($C3,Skater15,6,FALSE)*Result!$K$11)+(VLOOKUP($C3,Skater15,7,FALSE)*Result!$L$11)+((VLOOKUP($C3,Skater15,12,FALSE)+VLOOKUP($C3,Skater15,16,FALSE))*Result!$M$11)+(VLOOKUP($C3,Skater15,18,FALSE)*$N$11)+(VLOOKUP($C3,Skater15,22,FALSE)*Result!$O$11)+(VLOOKUP($C3,Skater15,23,FALSE)*Result!$P$11))*Result!$L$9)*(VLOOKUP($C3,Skater15,5,FALSE)/82)

[Special-K]

Apart from removing non-essential brackets I doubt you'll make it much shorter as each item is independent and there doesn't seem to be any pattern.
Looks like

Having found C3 in Skater
(Col6 * K11 + Col7 * L11 + (Col12+Col15) * M11 + Col18 * N11 + Col22 * O11 + Col23 * P11) * L9 * Col5 / 82

Not much of a pattern there

[Special-K]

New idea!

Create a table 2 columns by 7 rows

Please Login or Register  to view this content.

Then something like

=SUMPRODUCT(VLOOKUP($C3,Skater15,INDEX(Table!A1:B7,VLOOKUP(INDIRECT("1:"&ROW(COUNTA(Table!A1:A7))),1))*INDEX(Table!A1:B7,VLOOKUP(INDIRECT("1:"&ROW(COUNTA(Table!A1:A7))),2)))*L9 * VLOOKUP($C3,Skater15,9,FALSE)*$L$9/82

This is based on this

=SUMPRODUCT(--MID(H13,ROW(INDIRECT("1:" & LEN(H13))),1))

which adds up the digits of a number using INDIRECT to generate a sequence of rows.
Use that sequence to read down the table extracting the column number and cell to multiply against, SUMPRODUCT them as an array then multiply the L9 etc a the end of your formula as it doesnt fit the sequence.

Here's a reference to the original problem:

http://www.mrexcel.com/forum/excel-q...ml#post4257111

I believe this should work though I haven't got time to develop it.