# 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

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?

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

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),"")

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.

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?

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),"")

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.``

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

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?

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

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

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+

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1