• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

index with match formula

sgmpatnaik

sgmpatnaik

Active Member
Sir Kindly help me in the index with match formula


the sample book is attached below


https://dl.dropbox.com/u/75654703/Book1.xlsx


i tried but i am failed to success


kindly help


hence i oblige
 
Hi Patnaik ,


Check out the workbook at this link :


https://docs.google.com/open?id=0B0KMpuzr3MTVXzF1ODRGRTFaV3c


Narayan
 
Thank Q Mr. Narayank991 Sir,


it is working Great, and sorry for late replay my net is not working properly


Sir what is the mistake in my formula and why that is not working


One more request


IF I ADD THE SALE COLUMN THEN HOW CAN I DO THE SUM AND MULTIPLY


when i try to protect the workbook then the tab option is not working why this happen


With Regards
 
Hi Patnaik ,


A few points :


1. When checking for "OPC 43" use "OPC 43" , not "OPC" ; try using both :

[pre]
Code: 
=COUNTIF(Brand,"OPC")
and

[pre][code]=COUNTIF(Brand,"OPC 43")
[/pre]
and you will see the problem.


2. The ranges were not addressed properly ; when using a range such as B6:B1505 , be careful to make either one of them , or both of them , absolute ( using the $ symbol ) ; copying references such as B6:B1505 downwards , where both are relative , will result in the following :

B7:B1506
B8:B1507
B9:B1508
.
.[/code][/pre]
which is not what you want.


3. The use of COUNTA , COUNTIF and ROWS was not giving the correct , desired result ; when your range is B6:B1505 , the number of rows accessed is 1505 - 6 + 1 = 1500. Out of these , almost 1470 were blank. So when you look at SMALL(array,k) where k is obtained by using COUNTA , COUNTIF and ROWS , you should get 1470 or more ; only then you will be accessing the right values.


By not using the correct functions , you were always accessing the original table C6:C25 and beyond.


Regarding your other questions , can you please upload your workbook once more so that I can see what is to be done ?


Narayan
 
Thank Q Very Much Sir


and here is the Sample workbook


https://dl.dropbox.com/u/75654703/Book1.xlsx


Thanking you
 
Hi Patnaik ,


Can you tell me your exact requirement ?


I have downloaded your workbook , but I am not clear on what is to be done.


Narayan
 
Sure Sir,


have you seen the Sheet named as sTock


actually i want to know the stock position with brand wise that's why i create

Stock Sheet with different brands in different column, As per your instruction i got my result but there is one problem that is when i want to sum and less then the result is little bit wright and wrong


i am using the PPC Stock Column in Stock Sheet is A1:J1


our formula inserted in B1,E1,G1 and I1


Sum Range is D1:E1=result in F1

Less range is F1-I1=result in J1


and D1 Column value is link with J1


With Regards
 
Hi Patnaik ,


Can you tell me where you think the result is wrong ?


From what you have detailed , nothing seems wrong.


I assume all of your addresses are actually using row 6 i.e. A6 , B6 , E6 ,...


Narayan
 
sir the wrong result in Ob(D1) column Total(F1) Column and Balance(J1) i am getting the result as #value!from the row 24
 
Hi Patnaik ,


The reason for the #VALUE! results in columns D , F and J is that in the formulae in these three columns are dependent on the formulae in columns E and I.


The formulae in columns E and I use the IFERROR wrapper around the INDEX function , to display blanks where the INDEX function returns error values ; instead of returning blanks using "" as the second parameter to the IFERROR function , use 0 as the second parameter. To explain this , replace the existing formula in E6 :

[pre]
Code: 
=IFERROR(INDEX(Received_Qty,SMALL((Receive_Brand="PPC")*ROW(Receive_Brand),COUNTA(Receive_Brand)-COUNTIF(Receive_Brand,"PPC")+ROW(A1))-ROW(Receive_Brand)+1),"")
with the following formula :

[pre][code]=IFERROR(INDEX(Received_Qty,SMALL((Receive_Brand="PPC")*ROW(Receive_Brand),COUNTA(Receive_Brand)-COUNTIF(Receive_Brand,"PPC")+ROW(A1))-ROW(Receive_Brand)+1),0)
Copy the revised formula downwards to E7 , E8 and so on.


Similarly , replace the existing formula in I6 :

=IFERROR(INDEX(Sales_Qty,SMALL((Sale_Brand="PPC")*ROW(Sale_Brand),COUNTA(Sale_Brand)-COUNTIF(Sale_Brand,"PPC")+ROW(A1))-ROW(Sale_Brand)+1),"")
[/pre]
with the following formula :

=IFERROR(INDEX(Sales_Qty,SMALL((Sale_Brand="PPC")*ROW(Sale_Brand),COUNTA(Sale_Brand)-COUNTIF(Sale_Brand,"PPC")+ROW(A1))-ROW(Sale_Brand)+1),0)[/code][/pre]
Copy the revised formula downwards to I7 , I8 and so on.


With these changes , you will not get the #VALUE! errors. The #VALUE! error results because we are trying to add a numeric value and a blank ( which is text ).


Narayan
 
Ah Thank Q sir, it is working Great


Sir, if i need any help about this book shell i consult with you


With Regards


Patnaik
 
Hello Sir, Good Morning


i have small problem in that Workbook that's why i am back again with that question?


first of all i would like to inform you, i am using office 2007 and in some system office 2007 and office 2003. So which problem we were discuss there is no problem and it's working great but the problem in operating system of office2003, the reports are displaying as #NAME? in 2003 systems


Our Discuss formula is


=IFERROR(INDEX(Sales_Qty,SMALL((Sale_Brand="PPC")*ROW(Sale_Brand),COUNTA(Sale_Brand)-COUNTIF(Sale_Brand,"PPC")+ROW(A1))-ROW(Sale_Brand)+1),0)


Kindly suggest


With Regards


Patnaik
 
You must log in or register to reply here.
Back
Top