SOLVED
Home

INDEX MATCH column limit

%3CLINGO-SUB%20id%3D%22lingo-sub-740665%22%20slang%3D%22en-US%22%3EINDEX%20MATCH%20column%20limit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740665%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20am%20a%20newbie%20and%20searched%20for%20answer%20but%20did%20not%20see%20it%20-%20so%20I%20appreciate%20assistance...%3C%2FP%3E%3CP%3EI%20am%20doing%20a%20index%20match%20function%20-%20but%20I%20believe%20I%20am%20running%20into%20a%20limit%20in%20the%20column%20lookup%20to%20get%20my%20answer%20-%20my%20away%20is%20about%20400%20columns...the%20column%20with%20the%20answer%20is%20in%20column%20DA....my%20formula%20works%20well%20up%20to%20column%20CZ%20-%20but%20when%20it%20goes%20to%20DA%20is%20get%20%23ref!...I%20attempted%20to%20use%20a%20column%20Number%20(105)%2C%20a%20vlookup%2C%20and%20column%20Title%20(DA1)%20-%20all%20come%20back%20as%20ref...I%20appreciate%20any%20assistance%20-%20thanks%20in%20advance%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122411iB0734F822A02605F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22index%20match%20photo.jpg%22%20title%3D%22index%20match%20photo.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Ethe%20red%20numbers%20return%20ref%20answer%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-740665%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740698%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20column%20limit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371918%22%20target%3D%22_blank%22%3E%40gogglespasaino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20matrix%20is%20from%20column%20A%20to%20column%20CZ%20which%20correspond%20to%20columns%201%20to%20%3CSTRONG%3E104%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EThe%20formula%20part%20which%20calculates%20the%20column%20is%20COLUMN(DA1)%20which%20corresponds%20to%20column%20%3CSTRONG%3E105%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3ECan%20you%20spot%20the%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740816%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20column%20limit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740816%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU%20so%20much%20-%20I%20cannot%20believe%20I%20missed%20that%20-%20after%20looking%20at%20it%20for%20about%202%20hours%20!!!%20-%20LOL...%3C%2FP%3E%3CP%3EI%20have%20the%20same%20formula%20on%20multiple%20tabs...and%20the%20data%20on%20those%20arrays%20goes%20to%20CJ%20-%20so%20I%20thought%20CZ%20would%20have%20been%20fine%20(when%20I%20BLINDLY%20copies%20and%20pasted%20the%20formula)%20-%20not%20even%20realizing%20the%20data%20on%20the%20array%20that%20I%20am%20currently%20working%20on%20goes%20to%20column%20%22HV%22...AGAIN%20VERY%20MUCH%20APPRECIATED%20-%20my%20stress%20level%20as%20been%20reduced%20-%20all%20the%20best%20!!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
gogglespasaino
New Contributor

i am a newbie and searched for answer but did not see it - so I appreciate assistance...

I am doing a index match function - but I believe I am running into a limit in the column lookup to get my answer - my away is about 400 columns...the column with the answer is in column DA....my formula works well up to column CZ - but when it goes to DA is get #ref!...I attempted to use a column Number (105), a vlookup, and column Title (DA1) - all come back as ref...I appreciate any assistance - thanks in advanceindex match photo.jpgthe red numbers return ref answer

 

 

2 Replies
Solution

@gogglespasaino 

Your matrix is from column A to column CZ which correspond to columns 1 to 104.

The formula part which calculates the column is COLUMN(DA1) which corresponds to column 105.

Can you spot the problem?

 

@Detlef Lewin 

THANK YOU so much - I cannot believe I missed that - after looking at it for about 2 hours !!! - LOL...

I have the same formula on multiple tabs...and the data on those arrays goes to CJ - so I thought CZ would have been fine (when I BLINDLY copies and pasted the formula) - not even realizing the data on the array that I am currently working on goes to column "HV"...AGAIN VERY MUCH APPRECIATED - my stress level as been reduced - all the best !!!!!

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies