Filter formula - reference a particular column in a named table

[Trille]

Hi all!

I'm having some issues with a filter formula... Basically, I have a named table from which I want to return multiple results based on another cell's value (C7 in this particular case). This is the current formula which, in a perfect world, would return results from the table's column B, based on the values in column A (criteria found in C7 as seen in the formula below):

=TRANSPOSE(FILTER(INDEX(Table;;2);INDEX(Table;=C7;""))

This formula returns the #REF! error. When I remove the "2" in the first index, the formula returns values but from the first column. I'm not quite sure if I should add something to the second part of the formula, i.e. the "include" part?

Any help would be much appreciated!

/Trille

 

[jasonb75]

Going by the emoticon that is in the formula, I'd take a guess that you haven't specified a column number in the second index range.

Using the table and column name for the filter range and criteria would be a better way though.

 

[Trille]

Hi Jason!

Sorry for that.. should've hit "preview"... So the easiest way would basically be to define the dynamic range for each column instead of the idea above?

 

[jasonb75]

The smiley face thing catches everyone out, you don't expect it to crop up in the middle of a formula, but seeing it tells me that there is no number after the second semicolon in the second INDEX function. Adding a 1 to the formula at this point should fix the error.

=TRANSPOSE(FILTER(INDEX(Table;;2);INDEX(Table;;1)=C7;""))

For the alternative method, when you said

I have a named table

I assumed that you meant one created by going to Insert > Table on the excel ribbon.

If that is correct, then something like

=TRANSPOSE(FILTER(Table1[[Column2]:[Column2]];Table1[[Column1]:[Column1]]=C7;""))

should do it if you change the names used to match your actual table.

 

[Trille]

Thanks a lot for your help, Jason! I actually tried the first formula (with the added "1"), but kept getting an error... I have no idea why..

To solve the problem I just added the dynamic portion directly in the formula with OFFSET/COUNTA... It's not the prettiest, but it works.

Again, I really appreciate your time and effort!

 

[jasonb75]

It's not the prettiest, but it works

as long as there are no empty rows.

Both methods that I suggested work perfectly well when entered correctly. With the added 1 in the original formula, you wouldn't get a #REF! error, but without knowing the error that you got instead of #REF! there is no way of knowing what is wrong with it.