{"text_format":"3","workbook":"xlsx","n_sheets":1,"sheets":["Sheet2 (2)"],"not_rendered":{"macros":1},"read":"whole","date_system":"1900"}

{"sheet":"Sheet2 (2)","index":0,"n_rows":64,"n_cols":7,"rows_kept":64,"merged":[[44,13,45,15]]}
["Names","Innings","Runs",null,"Name","Sehwag"]
["Sachin",1,150,null,"Innings",1]
["Dravid",1,125,null,"Match",2]
["Sehwag",1,200]
["Sachin",2,120,null,"Runs",{"$f":"INDEX(C1:C14,SMALL(IF((A1:A14=F1)*(B1:B14=F2),ROW(C1:C14)),F3),1)","v":99},"with Array Formula…"]
["Dravid",2,101,{"$nulls":3},"{=INDEX(C1:C14,SMALL(IF((A1:A14=G1)*(B1:B14=G3),ROW(C1:C14)),G2),1)}"]
["Sehwag",2,51]
["2 nd Match",{"$nulls":4},{"$f":"INDEX(C1:C14,SUMPRODUCT(SMALL((A1:A14=F1)*(B1:B14=F2)*ROW(C1:C14),INDEX(FREQUENCY((A1:A14=F1)*(B1:B14=F2),0),1)+F3)),1)","v":99},"with Sumproduct"]
["Sachin",1,130,null,"=INDEX(C1:C14,SUMPRODUCT(SMALL((A1:A14=G1)*(B1:B14=G2)*ROW(C1:C14),INDEX(FREQUENCY((A1:A14=G1)*(B1:B14=G2),0),1)+G3)),1)"]
["Dravid",1,105]
["Sehwag",1,99,null,null,{"$f":"INDEX(C1:C14,NthMatch(F1&F2,INDEX(A1:A14&B1:B14,0),F3))","v":99},"with NthMatch and Index"]
["Sachin",2,110,null,null,"=INDEX(C1:C14,NthMatch(F1&F2,INDEX(A1:A14&B1:B14,0),F3))"]
["Dravid",2,111]
["Sehwag",2,75]
[]
[null,"So, the situation is all same.. As in the \"Sumproduct post\""]
[null,"ie."]
[null,"\"Wanna find Ist or 2nd innings Runs of each player… in Ist or 2nd Match….\""]
[]
[null,"what actually motivated me to further investigate what I am gonna explain now, is inspiration and cons of Sumproduct and "]
[null,"array formulae that my Sir XOR (an expert at ExcelForum.com) pointed out.."]
[]
[null,"they said.. There is not any smaller substitute of array construction.. And the resources used by the non array subsitutes are more than the "]
[null,"original array constructions.. (Constructions here means the Formula..)"]
[]
[null,"So here is the explanation.."]
[null,"the formula I used is "]
[null,"=INDEX(C1:C14,NthMatch(F1&F2,INDEX(A1:A14&B1:B14,0),F3))"]
[]
[null,"and for the row argument of Index Function, I used.."]
[null,"NthMatch(F1&F2,INDEX(A1:A14&B1:B14,0),F3)"]
[null,"ie."]
[null,"NthMatch(Lookup_Value,Lookup_Array,Match No.)"]
[]
[null,"I have explained the usage of this UDF in the separate post… but I am gonna Re-Explain It now.."]
[null,"Actually, It takes three arguments.."]
[]
[null,"First….. Lookup_Value ie = F1&F2 = \"Sehwag1\""]
[]
[null,"Second… Lookup_Array ie= INDEX(A1:A14&B1:B14,0)  "]
[null,"ie ="]
[null,"INDEX({\"NamesInnings\";\"Sachin1\";\"Dravid1\";\"Sehwag1\";\"Sachin2\";\"Dravid2\";\"Sehwag2\";\"2 nd Match\";\"Sachin1\";\"Dravid1\";\"Sehwag1\";\"Sachin2\";\"Dravid2\";\"Sehwag2\"},0)"]
[null,"and further equals to "]
[null,"{\"NamesInnings\";\"Sachin1\";\"Dravid1\";\"Sehwag1\";\"Sachin2\";\"Dravid2\";\"Sehwag2\";\"2 nd Match\";\"Sachin1\";\"Dravid1\";\"Sehwag1\";\"Sachin2\";\"Dravid2\";\"Sehwag2\"}"]
[]
[null,"Third…... Match No. ie = F3 = 2"]
[]
[null,"So the whole equation reduced to "]
["=NthMatch(\"Sehwag1\",{\"NamesInnings\";\"Sachin1\";\"Dravid1\";\"Sehwag1\";\"Sachin2\";\"Dravid2\";\"Sehwag2\";\"2 nd Match\";\"Sachin1\";\"Dravid1\";\"Sehwag1\";\"Sachin2\";\"Dravid2\";\"Sehwag2\"},2)"]
[]
[null,"Now what NthMatch Function will do is to find the 2nd match(Nth match) of \"Sehwag1\" in the Array  and return the row no. that is equal to 11"]
[]
[null,"Index function will do the rest.. "]
[null,"ie."]
[null,"=INDEX(C1:C14,11)"]
[null,"will return 99 .. That is our result…"]
[]
[null,"Other Explanations."]
[null,"I have used Index function to make an array of Player Names and Innings no.."]
[]
[null,"I hope, I have been able to explain it well.."]
[null,"Suggestions are always  invited.."]
[]
[null,"Vikas Gautam"]