Home

Need help in Index Match function

%3CLINGO-SUB%20id%3D%22lingo-sub-770340%22%20slang%3D%22en-US%22%3ENeed%20help%20in%20Index%20Match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770340%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20trying%20to%20Count%20no.s%20equals%20to%20ZERO%20from%20column%20DB%20with%20this%20formula%2C%20but%20getting%20error.%20Can%20anyone%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%202%20sheets%20in%20same%20workbook.%20I%20am%20trying%20to%20countif%20some%20data.%3C%2FP%3E%3CP%3EFormula%20used%20is%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(Dealer!%24D%3A%24D%2CState!%24B5%2CDealer!%24K%3A%24K%2C%22Active%22%2CDealer!DB%3ADB%2C%22%26lt%3B%3D0%22)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EThis%20formula%20works%20well.%20But...%3C%2FP%3E%3CP%3EThe%20case%20is%2C%20I%20don't%20want%20to%20use%20DB%3ADB%20in%20this%20formula%2C%20I%20want%20excel%20to%20refer%20this%20column%20automatically.%20That's%20why%20I%20tried%20using%20Index%20Match%20(referring%20current%20Month%20as%20%22July%22%20%26amp%3B%20%22B%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERefer%20cell%20DB1%20%3D%207B%3C%2FP%3E%3CP%3ERefer%20cell%20V1%20%3D%207B%20(in%202nd%20sheet)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(Dealer!%24D%3A%24D%2C%24B5%2CDealer!%24K%3A%24K%2C%22Active%22%2CINDEX(Dealer!%24CP%244%3A%24DE%241137%2C%2CMATCH(%24V%241%2CDealer!%24CP%241%3A%24DE%241%2C0))%2C%22%26lt%3B%3D0%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDon't%20know%20what%20is%20wrong%20in%20this%20formula.%20Please%20help.%3C%2FP%3E%3CP%3E%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%2F124210i043362D4106DC6C6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22sheet%202.jpg%22%20title%3D%22sheet%202.jpg%22%20%2F%3E%3C%2FSPAN%3E%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%2F124209i3868BB0FADF41CF2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22file%201.jpg%22%20title%3D%22file%201.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-770340%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-770452%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20Index%20Match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770452%22%20slang%3D%22en-US%22%3EIf%20a%20picture%20paints%20a%20thousand%20words%2C%20a%20sample%20Excel%20file%2C%20with%20dummy%20data%20and%20manually%20entered%20results%2C%20paints%20a%20million%20explanations!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-770458%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20Index%20Match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380971%22%20target%3D%22_blank%22%3E%40Sarav45%3C%2FA%3E%26nbsp%3B%2C%20in%20second%20INDEX%20the%20range%20shall%20be%20same%20size%2C%20like%3C%2FP%3E%0A%3CPRE%3EINDEX(Dealer!%24CP%3A%24DE%2C0%2CMATCH(%24V%241%2CDealer!%24CP%241%3A%24DE%241%2C0))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819374%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20Index%20Match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819374%22%20slang%3D%22en-US%22%3Ethank%20you%20so%20much%2C%20my%20bad....%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820854%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20Index%20Match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380971%22%20target%3D%22_blank%22%3E%40Sarav45%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Sarav45
Occasional Contributor

Hi, I am trying to Count no.s equals to ZERO from column DB with this formula, but getting error. Can anyone help me.

 

There are 2 sheets in same workbook. I am trying to countif some data.

Formula used is : 

=COUNTIFS(Dealer!$D:$D,State!$B5,Dealer!$K:$K,"Active",Dealer!DB:DB,"<=0")

This formula works well. But...

The case is, I don't want to use DB:DB in this formula, I want excel to refer this column automatically. That's why I tried using Index Match (referring current Month as "July" & "B")

 

Refer cell DB1 = 7B

Refer cell V1 = 7B (in 2nd sheet)

 

=COUNTIFS(Dealer!$D:$D,$B5,Dealer!$K:$K,"Active",INDEX(Dealer!$CP$4:$DE$1137,,MATCH($V$1,Dealer!$CP$1:$DE$1,0)),"<=0")

 

Don't know what is wrong in this formula. Please help.

sheet 2.jpgfile 1.jpg

4 Replies
Highlighted
If a picture paints a thousand words, a sample Excel file, with dummy data and manually entered results, paints a million explanations!

@Sarav45 , in second INDEX the range shall be same size, like

INDEX(Dealer!$CP:$DE,0,MATCH($V$1,Dealer!$CP$1:$DE$1,0))

 

thank you so much, my bad....

@Sarav45 , you are welcome

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
33 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies