Index with Match Formula

[sgmpatnaik]

Hi all


just i am stuck in the index with match function, i have one little bit doubt that is


'ColumnA ColumnB

'Product Qty

1 Pears 30

2 Orange 30

3 Banana 40

4 Pears 10

5 Orange 30

6 Pears 20


When i use the formula and copy to downward


=index(A1:B6,match("Pears",A1:A6,0),1)


off course i am getting the answer but as like here


'CoumnC

30

30

30

10

10

20


Why This Mistake Happen Actually i want the result like this


'ColumnC

30

10

20


Kindly Suggest

 

[Hui]

SP


Have a read of Lukes post at: http://chandoo.org/wp/2011/11/18/formula-forensics-003/


You could use something like this is C2:

=IFERROR(INDEX($B$1:$B$7,SMALL(($A$2:$A$7="Pears")*ROW($A$2:$A$7),COUNTA($A$2:$A$7)-COUNTIF($A$2:$A$7,"Pears")+ROWS($C$2:C2))),"")

This is an Array Formula so enter it with Ctrl Shift Enter

Then copy down

 

[sgmpatnaik]

Thank Q Sir


It's Working

 

[Montrey]

Luke is a God. I told ya. Man is a damn genius.

 

[sgmpatnaik]

Hello Hui Sir, Good After Noon


sir a little bit request for the index with match formula


as per our discuss the formula is working in the below range that is

A2:A7 and B1:B7


=IFERROR(INDEX($B$1:$B$7,SMALL(($A$2:$A$7="Pears")*ROW($A$2:$A$7),COUNTA($A$2:$A$7)-COUNTIF($A$2:$A$7,"Pears")+ROWS($C$2:C2))),"")


if we extend the range A2:A30 or more and B1:B30 or more then the answer is coming wrong why this happen?


Why we use the Range from B1:B7?


Kindly Suggest me sir


Thanking you