Return Mutliple data for same Value. Formula error

[roma83]

Hi I am a newbie.

I am currently working on excel. I needed a formula to return multiple data for the same value and found one from the internet and did some amendments to it according to my needs!

This is the formula I am using

=IF(ISERROR(INDEX(A:B,SMALL(IF(A:A=$E$1,ROW(A:A)),ROW($1:$1)),2)),"",INDEX(A:B,SMALL(IF(A:A=$E$1,ROW(A:A)),ROW($1:$1)),2))

However there seems to be something wrong with the formula as it is ignoring the reference in E1.
but when I check 'Function Arguments' formula is somehow returning the correct value!

I need the formula to return values according to the localities.


Could someone please help?
print screens attached for ease of reference

Thanks

Excel prnt scr.jpg

[sourabhg98]

This seems to be an array formula.
According to the pictures you uploaded, I can see that you have not entered them as array formulas.

Here is how you use array formulas-
Select cell you want to input the formula in.
Press F2 > Paste the formula
Now instead of pressing enter you need to press CTRL+SHIFT+ENTER
Check if this works.

[roma83]

Great it helped, you are a genius..... how did you realize that?

Teach me

[sourabhg98]

Glad to know that helped!
It is very much clear from your formula. In the Index formula, for the second argument (highlighted red) i.e. row number you have used arrays.
For the Index formula we know that the second argument i.e. row number has to be a number. You in the normal formula you can enter a single cell range but if you refer to the whole array i.e. A:A the formula only sees the first cell of the array i.e. A1 in this case. So the formula has to be array formula.

Moreover, from your pic I can see in the function bar (fx) that the formula is not entered as an array formula.
If it would have been an array formula you get curly braces around the formula like this >> {Your Formula}

Please Login or Register  to view this content.

[roma83]

Lovely... thanks for the explanation.

Always nice to learn.

THANKS a LOT again. MUCH APPRECIATED

[Tony Valko]

=IF(ISERROR(INDEX(A:B,SMALL(IF(A:A=$E$1,ROW(A:A)),ROW($1:$1)),2)),"",INDEX(A:B,SMALL(IF(A:A=$E$1,ROW(A:A)),ROW($1:$1)),2))

What version of Excel does this have to work in? If it's Excel 2007 or later you can reduce the length of the formula by half.

Also, you should avoid using entire columns for logical tests in array formulas (unless you're actually using every cell in the entire column!).

IF(A:A=$E$1 = Bad!

IF(A2:A1000=$E$1 = Much better!

[roma83]

Thanks a lot... will try to amend it

[roma83]

Further to my query... Formula seems to be working but not to the full.

The first line of each locality is being omitted

=IF(ISERROR(INDEX(List!$A:$D,SMALL(IF(List!A:A=$B$2,ROW(List!A:A)),ROW($A2:$C2)),2)),"",INDEX(List!$A:$D,SMALL(IF(List!A:A=$B$2,ROW(List!A:A)),ROW($A2:$C2)),2))

For instance if I have

Birkirkara - Joe Gatt
Birkirkara - Maria Riolo
Birkirkara - Vanna Demartino
Mosta - Melanie Buhagiar
Mosta - Francesco Gatt

Joe Gatt and Melanie Buhagiar are being omitted from the final list

Thanks again,

[roma83]

Further to my query... Formula seems to be working but not to the full.

The first line of each locality is being omitted

=IF(ISERROR(INDEX(List!$A:$D,SMALL(IF(List!A:A=$B$2,ROW(List!A:A)),ROW($A2:$C2)),2)),"",INDEX(List!$A:$D,SMALL(IF(List!A:A=$B$2,ROW(List!A:A)),ROW($A2:$C2)),2))

For instance if I have

Birkirkara - Joe Gatt
Birkirkara - Maria Riolo
Birkirkara - Vanna Demartino
Mosta - Melanie Buhagiar
Mosta - Francesco Gatt

Joe Gatt and Melanie Buhagiar are being omitted from the final list

Thanks again,