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
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....
Highlighted

@Sarav45 , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 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