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
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
Highlighted
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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies