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

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

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),"")
Glad it's working for you.

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.

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

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

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?

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