index match

[nparsons75]

Hi I have two identical tables, one on a sheet named March, another on a sheet named dashboard. I need to return the data from march into dashboard, simply by selecting a month from a dropdown list.

I will have the same as march but for each month of the year, by selecting the month on dashboard, the relevant data with appear on dashboard.

I think it will need index match but not sure. all help appreciated.

 

[cerfani]

You can use indirect for a dynamic range...

Assumming A1 has sheet name...

=INDIRECT(A1&"!A1")

that would reference A1 on the sheet selected in A1 of the current sheet, your index function index range will use the indirect

 

[nparsons75]

Hi, thanks for the reply...I have no idea how to use index match, I just thought it would need something like that. How would I use the formula you show, would these be part of an index match formula?

 

[cerfani]

oh yeah... you would do... and im just using random ranges...

Assuming A1 has the sheet name where you are searching, B1 is your lookup value, column A on the other sheet is what you want to return based on the value in column B ...

=INDEX(INDIRECT(A1&"!A:A"),MATCH(B1,INDIRECT(A1&"!B:B"),0))

instead of writing... Sheet2!A:A like how you would for an excel formula... you are building it as a string... A1 & "!A:A" ... then that goes in INDIRECT -> and Excel will convert the string into a range and use it in the formula like normal