index with match formula

[sgmpatnaik]

Hello Sir Good Afternoon All


sir i have small doubt when i am using the formula index with Match then i am getting #n/a instead of 0 or -


suppose i am using the data in Sheet1

[pre]

ColmA        ColumB
Row1   Product        Unit
Row2   Apple           10
Row3   banan.          20
Row4   apple           20
Row5   coconut         10
Row6   orange          05
row7   Apple           10
and i am using the formula in Sheet2 as given below:-


       CoulmA

Row1    =index('Sheet1'!A2:b7,match("Apple",'Sheet1'!a2:a7,0),2)


THE RESULT IS

ColumA
Row2    10
Row3    20
Row4    20
Row5    10
Row6    10
Row7    10
Row8    #N/A

[/pre]
Kindly Help

 

[xld]

Use


=IFERROR(INDEX(Sheet1!A2:B7,MATCH("Apple",Sheet1!A2:A7,0),2),"")


or


=IF(ISNA(MATCH("Apple",Sheet1!A2:A7,0)),"",INDEX(Sheet1!A2:B7,MATCH("Apple",Sheet1!A2:A7,0),2))


for Excel 2003

 

[sgmpatnaik]

Thank You Mr Xlx let me try this


and one more request if extend the range then also it will work na

 

[sgmpatnaik]

Hello Mr Xld,


It is working but there is one problem


If we extend or change the name then we get the repeating


which i mentioned in my question of the THE RESULT IS


Please check the The result option


Kindly help

 

[Luke M]

Assuming that the word you want (i.e. "Apple" is in cell D2. formula becomes:

=IF(COUNTIF(Sheet1!A:A,$D$2)<ROW(A1),"-",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B$1:$B$100=$D$2,ROW(Sheet1!$B$1:$B$100)),ROW(A1))))


Confirm this array formula using Ctrl+Shift+Enter, not just Enter. Copy down as far as would ever be needed.


Don't change any of the A1 references, they are simply counters. The only range that might need to be increase is B1:B100. Make this a reasonably large to cover any scenario.


Formula explained here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/