Jan 13 2021 08:12 PM
How do I use index match formula to pull a similar type of data from different sheets.Example I would like to pull different dam storage data from different sheet for a particular year and month.
Jan 14 2021 02:37 AM
It very depends on how your data is structured, could you provide small sample file to illustrate?
Jan 14 2021 05:46 AM
I attached here is example of what I am trying to do.Basically I want to pull the data to my master so that I can create a dynamic chart.
Jan 14 2021 07:08 AM
I'd transform other sheets data to tables (DamA, DamB, etc), it'll be more flexible. With that
Years:
=TRANSPOSE(INDIRECT($F$3 & "[Year]"))
Data:
=TRANSPOSE(INDIRECT($F$3 & "[" & $B7 & "]" ))
like
Jan 14 2021 05:28 PM
Since my knowledge of excel is quite limited, Can I say that there is no need for index match in this case as all you need is to indirectly transpose the results.
Jan 14 2021 06:32 PM
I am a little confused as I did not see you reference any of the data sheets. I also attached another example for you to look at. Can this be done by index match?
Jan 14 2021 11:46 PM
Attempting to apply the same method that Sergei used, I believe the attached file is what you are trying to do? Since he is using structured tables (naming the 3 tables DamA, DamB, DamC), he can reference them by table name and column name, using the indirect function, instead of referencing the sheet they are on (format is "Table[Field]").
For this one, index/match would be appropriate to pull the specific year (row) from the table.
Jan 15 2021 12:48 AM
In addition to @JMB17 post - if for some reasons you don't want to use Tables (which is highly recommended) at least clean the structure of your sheets. Start in each range in the same cell of the sheet, e.g. in C4, not in any random place. Not critical, but desirably to have same column names, e.g. Jun for the month in all places, not Jun or June. With that and assuming you have no other data down fro each range, formula in C7 of master sheet could be
=IFNA(
INDEX(
INDIRECT("'" & $H$3 & "'!$D$5"):
INDEX( INDIRECT("'" & $H$3 & "'!$D$5:$O$200"), COUNTA( INDIRECT("'" & $H$3 & "'!$C$5:$C$200")),12),
MATCH($B7,INDIRECT("'" & $H$3 & "'!$C$5"):
INDEX(INDIRECT("'" & $H$3 & "'!$C$5:$C$200"),
COUNTA(INDIRECT("'" & $H$3 & "'!$C$5:$C$200"))),0),
COLUMN()-COLUMN($B$5) ),
"-")
and drag it to the right.
One more comment - please mention on which version of Excel you are (365, 2016, Online, etc).
Jan 15 2021 01:00 AM
I am using excel version 2016.
Jan 15 2021 01:06 AM
It is not that I don't want to use table by rather I am new to using tables.
Jan 24 2021 03:37 AM
Looking through your last example I am confused and find that your formula is not working with Dam C as that dam has different dates.
Jan 24 2021 03:41 AM
Can you kindly explain why you leave out matching the month in your formula.Thank you.
Jan 24 2021 11:45 PM - edited Jan 24 2021 11:47 PM
Since the master sheet presents the data in the same order as the individual data sheets, it's not really necessary to match the columns, so it's returning all of the columns on the row to which it matched (as Sergei did also, if I remember correctly).
The INDEX function is capable of returning all rows and/or all columns of whatever row/column it matches to. This option is selected by inputting a "0" in either the row or column argument.
=INDEX(MATCH(), 0) > returns all columns of the row to which it matches.
=INDEX(0, MATCH()) > returns all rows of the column to which it matches.
Since the formula is returning multiple values, you have to select a range that is the same dimension as the table that the formula will return (B6:N6) when you enter the formula (though newer versions of Excel with dynamic arrays activated should not require this I think).
If your months on the master sheet were in a different order, or were only for certain months, then you could match the month to the table header rows in the data sheets by replacing the second argument of the INDEX function (0) with another match function:
=IFERROR(INDEX(INDIRECT($H$3),MATCH($J$3,INDIRECT($H$3&"["&$B$5&"]"),0),MATCH(B$5,INDIRECT($H$3&"[#Headers]"),0)),0)