Index match with multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2059354%22%20slang%3D%22en-US%22%3EIndex%20match%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2059354%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20use%20index%20match%20formula%20to%20pull%20a%20similar%20type%20of%20data%20from%20different%20sheets.Example%20I%20would%20like%20to%20pull%20different%20dam%20storage%20data%20from%20different%20sheet%20for%20a%20particular%20year%20and%20month.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2059354%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2060156%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2060156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F921906%22%20target%3D%22_blank%22%3E%40Liphor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20very%20depends%20on%20how%20your%20data%20is%20structured%2C%20could%20you%20provide%20small%20sample%20file%20to%20illustrate%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2060827%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2060827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EI%20attached%20here%20is%20example%20of%20what%20I%20am%20trying%20to%20do.Basically%20I%20want%20to%20pull%20the%20data%20to%20my%20master%20so%20that%20I%20can%20create%20a%20dynamic%20chart.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061111%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F921906%22%20target%3D%22_blank%22%3E%40Liphor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20transform%20other%20sheets%20data%20to%20tables%20(DamA%2C%20DamB%2C%20etc)%2C%20it'll%20be%20more%20flexible.%20With%20that%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EYears%3A%0A%3DTRANSPOSE(INDIRECT(%24F%243%20%26amp%3B%20%22%5BYear%5D%22))%0A%0AData%3A%0A%3DTRANSPOSE(INDIRECT(%24F%243%20%26amp%3B%20%22%5B%22%20%26amp%3B%20%24B7%20%26amp%3B%20%22%5D%22%20))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Elike%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20519px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246402i660838D0A1191A1B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2062935%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2062935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20my%20knowledge%20of%20excel%20is%20quite%20limited%2C%20Can%20I%20say%20that%20there%20is%20no%20need%20for%20index%20match%20in%20this%20case%20as%20all%20you%20need%20is%20to%20indirectly%20transpose%20the%20results.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2063110%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2063110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20little%20confused%20as%20I%20did%20not%20see%20you%20reference%20any%20of%20the%20data%20sheets.%20I%20also%20attached%20another%20example%20for%20you%20to%20look%20at.%20Can%20this%20be%20done%20by%20index%20match%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2063459%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2063459%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F921906%22%20target%3D%22_blank%22%3E%40Liphor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttempting%20to%20apply%20the%20same%20method%20that%20Sergei%20used%2C%20I%20believe%20the%20attached%26nbsp%3B%20file%20is%20what%20you%20are%20trying%20to%20do%3F%20Since%20he%20is%20using%20structured%20tables%20(naming%20the%203%20tables%20DamA%2C%20DamB%2C%20DamC)%2C%20he%20can%20reference%20them%20by%20table%20name%20and%20column%20name%2C%20using%20the%20indirect%20function%2C%20instead%20of%20referencing%20the%20sheet%20they%20are%20on%20(format%20is%20%22Table%5BField%5D%22).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20this%20one%2C%20index%2Fmatch%20would%20be%20appropriate%20to%20pull%20the%20specific%20year%20(row)%20from%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

9 Replies

@Liphor 

It very depends on how your data is structured, could you provide small sample file to illustrate?

@Sergei Baklan,

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.

@Liphor 

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

image.png

@Sergei Baklan 

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.

@Sergei Baklan 

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?

@Liphor 

 

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.

 

@Liphor 

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).

@Sergei Baklan 

I am using excel version 2016.

@Sergei Baklan 

It is not that I don't want to use table by rather I am new to using tables.