Index match

[baker74]

Hello,

Bear with me as i try to explain what I would like to do....

I currently have two spreadsheets one being the master document and the second having the data from with to populate the master. I currenty have an index match formula that matches data from 2 columns from the master to 2 columns of the second spreadsheet and when a positive matches on the 2 criteria are made, the formula returns the data from column 10 from second spreadsheet to the master. works perfectly.

Now for what i would like to do... As it is, the second spreadsheet is a compulation of 6 different sheets. In order for the formula to work, I have to combine all data first into one tab or sheet. I would love to leave the workbook in the state i get it which has six tabs or sheets. How do I index and match from 6 different tabs or sheets in the same workbook and return that data to the master sheet? Below is my current formula.

=INDEX('C:\EP\[ALL ROUTES COMBINED Q1 2013.xlsx]Route A'!$C$13:$O$462,MATCH(A17&C17,'C:\EP\[ALL ROUTES COMBINED Q1 2013.xlsx]Route A'!$C$13:$C$462&'C:\EP\[ALL ROUTES COMBINED Q1 2013.xlsx]Route A'!$E$13:$E$462,0),10)

[JBeaucaire]

Imagine that long, extremely hard to read formula get 6 times more difficult and complex.

What you're doing sound like the exact right thing to do.

[baker74]

my problem is that i dont know what separators to use to add more match statements to this formula.

[JBeaucaire]

One simplification I can offer is to add a helper column to the DATA itself to make this lookup not have to work so hard in every cell. It looks like your concatenating the columns C & E in the external sheet. Let's say you wanted to add G and H as well. IN that external sheet, assume column Z is empty.

1) In Z13 but this helper formula: =C13&"-"&E13&"-"&G13&"-"&H13
2) Copy Z13 down the whole dataset
You now have a single "lookup cell" for each row, much easier from this point on.
3) Save and close the external workbook


4) In lookup workbook, let's say you're on row 17 and wanting to match A, C, D and E to the external workbook's helper column. You don't really need to set ranges on the INDEX nor the match, it's robust enough to stop when it finds a match.

=INDEX('C:\EP\[ALL ROUTES COMBINED Q1 2013.xlsx]Route A'!$L:$L,MATCH(A17&"-"&C17&"-"&D17&"-"&E17, 'C:\EP\[ALL ROUTES COMBINED Q1 2013.xlsx]Route A'!$Z:$Z, 0))

[baker74]

i am not really doing a concat. i basically have a spreadsheet that has all my locations combined and i need to know the inventory in the chemical tanks. however, for any given location there can be more than one tank with different chemical in it. so what i am doing is looking and each location and the chemical and then matching that to the route sheets and finding that match of location and chemical and then returning that inventory for that specific chemical tank. i would like the formula to look at the first tab and try and find the location and product and return the inventory, but if that match isnt in the first tab, go the next and so on until the match is made. make since? should i send an example?

[JBeaucaire]

You'll need to create a separate version of each INDEX/MATCH looking at each different sheet. Then you can nest them together using IFERROR

Separate examples:

1st sheet: =INDEX([OtherBook.xls]Sheet1!range, MATCH(A17&C17, [OtherBook.xls]Sheet1!range, 0))
2nd sheet: =INDEX([OtherBook.xls]Sheet2!range, MATCH(A17&C17, [OtherBook.xls]Sheet2!range, 0))
3rd sheet: =INDEX([OtherBook.xls]Sheet3!range, MATCH(A17&C17, [OtherBook.xls]Sheet3!range, 0))



Nested example:
=IFERROR(IFERROR(INDEX([OtherBook.xls]Sheet1!range, MATCH(A17&C17, [OtherBook.xls]Sheet1!range, 0)), INDEX([OtherBook.xls]Sheet2!range, MATCH(A17&C17, [OtherBook.xls]Sheet2!range, 0))), INDEX([OtherBook.xls]Sheet3!range, MATCH(A17&C17, [OtherBook.xls]Sheet3!range, 0))

[baker74]

=IFERROR(INDEX('C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route A'!$C$13:$O$462,MATCH(A2&C2,'C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route A'!$C$13:$C$462&'C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route A'!$E$13:$E$462,0),10),INDEX('C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route B'!$C$13:$O$462,MATCH(A2&C2,'C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route B'!$C$13:$C$462&'C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route B'!$E$13:$E$462,0),10),INDEX('C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route C'!$C$13:$O$462,MATCH(A2&C2,'C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route C'!$C$13:$C$462&'C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route C'!$E$13:$E$462,0),10),INDEX('C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route D'!$C$13:$O$462,MATCH(A2&C2,'C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route D'!$C$13:$C$462&'C:\EP Energy\[ALL ROUTES COMBINED Q1 2013.xlsx]Route D'!$E$13:$E$462,0),10))


This is my formula. I have colored sections by each route sheet I am searching… please not the “10” at the end of each index. This is the column that has the inventory number I want to return…. Excel says I am missing arguments or parenthesis… killing me!
index formula.docx

[JBeaucaire]

See my original formula, it INDEXes column L which is your column 10 in your original range. By only INDEXing the column you want a result from, you don't need that third parameter. Then the MATCH range is column C in your formula, column Z in my example using a helper column.

I really think you should simplify the formulas down in the manner I've suggested.


Now, show me the separate formulas that each work on their own?

[baker74]

I have simplified the formulas as you suggested with the helper columns. It works perfectly. Now i am trying to add all 4 routes to the query. Excel doesnt like the syntax however. Do you see what the issue is?

=IFERROR(INDEX('[ALL ROUTES COMBINED Q1 2013-2.xlsx]Route A'!$M$13:$M$137,MATCH(A2&"-"&C2,'[ALL ROUTES COMBINED Q1 2013-2.xlsx]Route A'!$Z$13:$Z$137,0))),(INDEX('[ALL ROUTES COMBINED Q1 2013-2.xlsx]Route B'!$M$13:$M$137,MATCH(A2&"-"&C2,'[ALL ROUTES COMBINED Q1 2013-2.xlsx]Route B'!$Z$13:$Z$137,0))), (INDEX('[ALL ROUTES COMBINED Q1 2013-2.xlsx]Route C'!$M$13:$M$137,MATCH(A2&"-"&C2,'[ALL ROUTES COMBINED Q1 2013-2.xlsx]Route C'!$Z$13:$Z$137,0))), (INDEX('[ALL ROUTES COMBINED Q1 2013-2.xlsx]Route D'!$M$13:$M$137,MATCH(A2&"-"&C2,'[ALL ROUTES COMBINED Q1 2013-2.xlsx]Route D'!$Z$13:$Z$137,0)))

[JBeaucaire]

I would expect to see 3 IFFEROR() wraps for 4 possible formulas.

=Formula1
=Formula2
=Formula3
=Formula4

becomes...


=IFERROR(IFERROR(IFERROR(Formula1, Formula2), Formula3), Formula4)

[baker74]

Got it!!!! I have it searching 6 routes flawlessly! thanks so much for your help!

[JBeaucaire]

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.