# Problem with Index and Small functions to lookup and display multiple cells

1. ## Problem with Index and Small functions to lookup and display multiple cells

Hi All,

I am unable to upload a test sheet at the moment but Imagine Sheet 1 has 2 columns, Column A lists AREA numbers (i.e AREA A, AREA B etc), Column B lists the branch numbers in those area's (I.e EB101, RF230, CD605).
On sheet 2 i have a Cell (C2) where i want to enter an AREA number and then on the same sheet in B7:B20 i am trying to put an equation in that will list all the Branch numbers in that Area, I have started using the SMALL function in order to get the row numbers...

=SMALL(IF(Sheet1!\$A\$2:\$A\$100=Sheet2!\$C\$2,ROW(Sheet1!\$A\$2:\$A\$100)),ROW(1:1))

i am pretty sure i need to use the INDEX funtion at the beggning in order to show the contents of the Cell that the Small function has found the row number for but have no idea how to get it to work.

Any idea's?

Thanks  Register To Reply

2. ## Re: Problem with Index and Small functions to lookup and display multiple cells

Try
=INDEX(Sheet1!\$B\$2:\$B\$100,SMALL(IF(Sheet1!\$A\$2:\$A\$100=Sheet2!\$C\$2,ROW(Sheet1!\$A\$2:\$A\$100)-1),ROW(1:1)
Note that I added a "-1" to your formula also as you are starting with row 2.
This is an array formula and must be entered with CNTRL SHFT ENTER
Questions?  Register To Reply

3. ## Re: Problem with Index and Small functions to lookup and display multiple cells Originally Posted by ChemistB Try
=INDEX(Sheet1!\$B\$2:\$B\$100,SMALL(IF(Sheet1!\$A\$2:\$A\$100=Sheet2!\$C\$2,ROW(Sheet1!\$A\$2:\$A\$100)-1),ROW(1:1)
Note that I added a "-1" to your formula also as you are starting with row 2.
This is an array formula and must be entered with CNTRL SHFT ENTER
Questions?
Brilliant that worked well, I was doing similar but didnt put the -1 in which is probably why the best result i was getting was 0.

Also after dragging the equation to the cells below in order to list all the matching critea, the cells that cannot find a match have #num, can i get rid of this? maybe using the IF(ISERROR equation?

Thanks  Register To Reply

4. ## Re: Problem with Index and Small functions to lookup and display multiple cells

Yes,

IFERROR(=INDEX(Sheet1!\$B\$2:\$B\$100,SMALL(IF(Sheet1!\$A\$2:\$A\$100=Sheet2!\$C\$2,ROW(Sheet1!\$A\$2:\$A\$100)-1),ROW(1:1),"")  Register To Reply

5. ## Re: Problem with Index and Small functions to lookup and display multiple cells Originally Posted by ChemistB Yes,

IFERROR(=INDEX(Sheet1!\$B\$2:\$B\$100,SMALL(IF(Sheet1!\$A\$2:\$A\$100=Sheet2!\$C\$2,ROW(Sheet1!\$A\$2:\$A\$100)-1),ROW(1:1),"")
Nope for some reason that did not work, It seems to just have the complete equation written in the cell, should it have an = at the beginning in order to tell excel that it is an equation?

Thanks for the help.  Register To Reply

6. ## Re: Problem with Index and Small functions to lookup and display multiple cells

Yes, you would need the = sign in front of what I showed in post 4 for it to work. Does that work properly now?  Register To Reply

7. ## Re: Problem with Index and Small functions to lookup and display multiple cells Originally Posted by ChemistB Yes, you would need the = sign in front of what I showed in post 4 for it to work. Does that work properly now?
Nope still not working, i get "the formula you typed contains and error".

This is what i am typing..

=IFERROR(INDEX(Sheet1!\$B\$2:\$B\$100,SMALL(IF(Sheet1!\$A\$2:\$A\$100=Sheet2!\$C\$2,ROW(Sheet1!\$A\$2:\$A\$100)-1),ROW(1:1),"")  Register To Reply

8. ## Re: Problem with Index and Small functions to lookup and display multiple cells

Hello,

You missed some parentheses, try this one instead ``Please Login or Register  to view this content.``  Register To Reply

9. ## Re: Problem with Index and Small functions to lookup and display multiple cells Originally Posted by Lemice Hello,

You missed some parentheses, try this one instead ``Please Login or Register  to view this content.``
That worked, Many thanks  Register To Reply

10. ## Re: Problem with Index and Small functions to lookup and display multiple cells

Hi,

That IFERROR worked fine in my computer with excel 2010 but on my work computer which runs 2003 it doesnt work, I have read that I can use =IF (ISERROR but cannot get it to work.

Any ideas?  Register To Reply

11. ## Re: Problem with Index and Small functions to lookup and display multiple cells

So try in B7: ``Please Login or Register  to view this content.``
(With B6 is not blank. If B6 is blank, remove "-1" (red in formula))
Ctrl-Shift-Enter  Register To Reply

12. ## Re: Problem with Index and Small functions to lookup and display multiple cells Originally Posted by bebo021999 So try in B7: ``Please Login or Register  to view this content.``
(With B6 is not blank. If B6 is blank, remove "-1" (red in formula))
Ctrl-Shift-Enter
Brilliant, I will try this when at work, Any chance in giving me a breakdown on what that equation is actually doing?

Thanks  Register To Reply

13. ## Re: Problem with Index and Small functions to lookup and display multiple cells

This is a long story, so it is better to upgrade to 2007+   Register To Reply