Home

Excel Formula issue

%3CLINGO-SUB%20id%3D%22lingo-sub-918399%22%20slang%3D%22en-US%22%3EExcel%20Formula%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-918399%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20i%20am%20having%20an%20issue%20with%20a%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%20A24%20%3D%20STATS!B1%3AB32%2C%20STATS!AK1%3AAK32%2C%20%22%22)%20produces%20an%20error%20everytime%2C%20Basically%20what%20I%20am%20trying%20to%20do%20is%20say%20if%20anything%20of%20column%20B%20is%20the%20same.%20I%20want%20it%20to%20pull%20information%20from%20that%20same%20row%20and%20put%20it%20on%20a%20different%20page%20of%20the%20workbook.%20However%2C%20this%20does%20work.%20%3DIF(A24%20%3D%20%22VGK%22%2C%20STATS!AK1%3AAK32%2C%20%22%22).%20SO%20it%20has%20to%20be%20something%20i%20am%20doing%20with%20column%20B%20on%20the%20STATS%20worksheet.%20I%20think%20it%20is%20trying%20to%20add%20all%20of%20the%20columns%20up%20to%20equal%20that%20one%20cell%2C%20but%20obviously%20that%20creates%20an%20error%2C%20How%20do%20I%20fix%20this%20thanks%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-918399%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
hoopghetto
Occasional Visitor

Hello,

 

So i am having an issue with a formula.

 

=IF( A24 = STATS!B1:B32, STATS!AK1:AK32, "") produces an error everytime, Basically what I am trying to do is say if anything of column B is the same. I want it to pull information from that same row and put it on a different page of the workbook. However, this does work. =IF(A24 = "VGK", STATS!AK1:AK32, ""). SO it has to be something i am doing with column B on the STATS worksheet. I think it is trying to add all of the columns up to equal that one cell, but obviously that creates an error, How do I fix this thanks? 

3 Replies

@hoopghetto 

It's always helpful if you upload a sample file with not working formula and desired output mocked up manually to visualize what exactly you are trying to achieve.

 

Btw, please try this and see if this is what you are trying to achieve.

 

=IFERROR(INDEX(STATS!$AK$1:$AK$32,MATCH(A24,STATS!$B$1:$B$32,0)),"")

@hoopghetto 

Another alternative would be: 

=IFNA(LOOKUP(2,1/(

STATS!B$1:B$32=A24),

STATS!AK$1:AK$32),

"")

The reason for the error is IF does not treat B1:B32 as an array. Excel isn't checking all those cells to see if A24 is present. As the others have advised, you'll need a lookup function.

If you're running a fully updated version of O365 you may get a spilled result with your original formula. Excel is getting smarter!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies