Cell referencing change with filter

Question

I am quite new to Excel and just figuring out how to do things. I have a question about cell referencing between sheets.

Say I have on Sheet1 this table:

DIMENSION  VALUE
X          0
Y          1
Z          2
Z          3

and on Sheet2 I have the following:

FILTER(DIMENSION)  VALUE
    X              0

So basically I have a filter that selects the dimension values from Sheet 1. How can I reference the values for each dimension so that when I change the filter, it reflects the correct values?

So basically, if the filter is set to X then it would show the above. But if the filter is set to Z then:

FILTER(DIMENSION)  VALUE
    Z              2
    Z              3

My questions is, how do I make the cell referencing in Sheet 2 reflect what is shown in Sheet 1, based on the value of the filter?

Answer 1

Welcome @MOA. You have two options. One is using formulae, along the lines that you are thinking. The second, perhaps better and easier method, is using Excel's built-in Filter feature. For this you don't even need a second sheet.

Have a search for "how to use Filters in Excel" on youtube. In brief, select your header in sheet1 (A1 to B1). Click Home ribbon > Sort and Filter > Filter. You will now see little down arrows appear next to each column header. Proceed to click the arrow next to dimension, and use the checkboxes to select only the required dimensions.

As for the formula method, in sheet2, cell B2 you can use the VLOOKUP function like this

=VLOOKUP(A2,Sheet1!$A$2:$B$5$,2,FALSE)

However, this will only work for a single value (i.e. for X and first value of Z). To return more than one results requires helper columns in sheet1 or more advanced array formula. But first let me know if the built-in filter works for you.

Answer 2

With Office 365, you have the FILTER function.

So a formula along the lines of:

Sheet2!B2: =FILTER(Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5=A2)

Will return :

*Note how the result SPILL's down if there is more than one match.