# Powerpivot handling over 64 arguements in a formula

### Question

• SO power pivot can handle x amount of arguments in each formula but when I try to do the same formula on a standard excel sheet it errors out with the too many arguments.

The formula just says if a=1 then XX1, if a=2 then XX2, etc for about 80-100 arguments.

I've seen ideas where you can make a 2nd reference table that has 2 columns like...

Letter       Doc #

a              XX1

b              XX2

c              XX3

Then something have your column like...

=IFERROR(VLOOKUP('data sheet'!C2, [Doc #], 2, False), "")

But I don't really understand this formula.

Wednesday, July 24, 2013 2:46 PM

### All replies

• you can recreate the VLookup Excel formula in PowerPivot using the RELATED or RELATEDTABLE function. Here's a resource: Lookups in PowerPivot Formula's

Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

Wednesday, July 24, 2013 5:02 PM
• you can recreate the VLookup Excel formula in PowerPivot using the RELATED or RELATEDTABLE function. Here's a resource: Lookups in PowerPivot Formula's

That's not the problem.

My formula "(IF([Letter]="A", "XX1", IF([Letter]="B", "XX2", etc... "works fine in powerpivot. When I try to use it on a normal excel sheet it is over the 64 arguement. How would I be able to work around this 64 argument limit. I'm using Excel 2010.

Wednesday, July 24, 2013 6:28 PM
• Sorry about that. And I am not aware of workarounds for argument limits in excel formula. As you said, VLookup seems to be the solution but bsaed on your original thread, you wanted more information about VLOOKUP formula, this may help: http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

Wednesday, July 24, 2013 6:41 PM