Excel - Struggling with complex Index/Match/Match lookup

Question

I've got a table with 9 columns and about 6000 rows. Each row has a price as the last column. Some of those prices are 0.00 when they should be a value.

In another worksheet I have the "original" table with about 3700 rows. The prices I need are in those rows. However, the original table has the prices horizontally within the rows, each next to a cell with specific range of gals. Basically the table I have has unique rows for every location/gal range/price combo, the original has all gal range/prices sequentially in single location rows

For example, a row in the original table looks like this:

... / 1-2000 / 2.8383 / 2001-4000 / 2.5382 / ...

Where as in my new table they look like this:

... / 1-2000 / 2.8383
... / 20001-4000 / 2.5382
etc

Everything is the same in my new table and the original table EXCEPT those gal ranges and prices.

What I'm trying to do is use an array multiple criteria Index/Match (based on 3 cells in both my new table and the original) to lookup the row, find the value that matches the range of gals, and then take the price to the right of that gal range cell.

Here is a row I'm trying to get the value for in my new table:

Here is the row with the value I need in the original table:

Here's a closer look at the formula I've constructed:

INDEX(old!$A$2:$Q$3755,MATCH(1,(A29=old!$A$2:$A$3755)*(F29=old!$F$2:$F$3755)*(G29=old!$G$2:$G3755),1),MATCH(H29,old!$J$2:$Q$3755,1)+1)

The first standard Index/Match part works great... I Index the table and Match to find the row. If I just enter a number for the Col (e.g., 1, 2, 3) it'll return the value from the corresponding cell PERFECTLY. However, I can't seem to make the Col match part work... I continuously get REF and N/A errors.

Is there some trick to doing a two way search? It seems like it should be a simple thing to just find that value in the row and take the next cell after if...?

One catch here is that the gal range value I'm looking for is NOT unique... there are at least 20 other references that have the same range (e.g., "1-2000"). Is there a way to limit the col match to just the row I find with the row match?

Any help is GREATLY appreciated.

Thanks, Rick

You use a lot of words, but it's still not clear what your data looks like. The only difference I can see from your sample are three dots in the middle instead of the end. It would greatly help to see a real data sample in a real Excel grid. Screenshot or workbook on an ad-free service like DropBox or OneDrive will work. A simplified example rather than your whole workbook. Edit the question. then post a comment. Do not post clarification in comments. — teylyn
Sorry, I had thought my explanation was pretty thorough. Unfortunately sharing the raw data isn't feasible since it's sensitive. Basically I need some insight on why a match for a col number would fail in the Index Match formula provided above. — Rick Tilghman
In an Index(match,match) combo, the second match identifies the column and typically looks in one row only, i.e. the title row, where the title of the column is found and matched. You are giving it the whole table. is that intended? — teylyn
Yeah, sorry, Stack modified those quick comparison samples... the second sample should have had the gal ranges/prices on different lines (rather than the same line, as they are in the original). To your second comment, okay, well, col match to just the first row (headers) won't work since the value I'm looking for could be in any of 5 different columns per row (e.g., the gal range could be "1500-3000", and that could be in col 7, col 9, col 11, etc). I need to look for the gal range value in the row that Index/Match returns and then get the value in the cell next to it if that makes sense? — Rick Tilghman

Terry W Terry W · Accepted Answer · 2019-09-13T05:23:26

One way to tackle this case is to use #powerquery.

Please refer to this article to find out how to use Power Query on your version of Excel. It is availeble in Excel 2010 Professional Plus and later versions. My demonstration is using Excel 2016.

The steps are:

Load/Add your old data to the Power Query Editor. My sample data only has one row but it is same for thousands of rows;

Use Merge Columns function under Transform tab to merge the first 7 columns with a delimiter say semicolon ;;
Repeat Merge Columns for each pair of GALLONS and TOTAL PRICES with the same delimiter ;. If you have done it correctly you should have something like the following:

Use Unpivot Columns function under Transform tab to unpivot all the merged columns for GALLONS;TOTAL PRICE, then remove the Attribute column;

Use Split Column function under the Transform tab to split each column by delimiter ;. If you have done it correctly you should have something like the following:

Make a duplicate column of the GALLONS range column (which is the second last column in the above screen-shot), and then split the original GALLONS range column by delimiter -. Then you should have:

Rename the column headers as desired;

Close & Load the new table to a new worksheet (by default) or you can change the default setting and create a connection for the new table and load it to a desired location in your workbook.

The second table is the output table and you can do INDEX+MATCH from this new table which should be much easier than from the old table. If the data are identical but just in different structure then you may just use the output table without worrying about looking up missing prices.

I have added a test line to my source table and here is the Refreshed output with a click of button:

Here are the power query M codes behind the scene for reference only. All steps are performed using built-in functions of the editor which is quite straight forward.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IATA", Int64.Type}, {"ST", type text}, {"FUELER", type text}, {"UPDATED", type datetime}, {"RESTRICTIONS", type text}, {"BASEF UEL", type text}, {"NOTES", type any}, {"GALLONS1", type text}, {"TOTAL PRICES1", type text}, {"GALLONS2", type text}, {"TOTAL PRICES2", type text}, {"GALLONS3", type text}, {"TOTAL PRICES3", type text}, {"GALLONS4", type text}, {"TOTAL PRICES4", type text}, {"GALLONS5", type text}, {"TOTAL PRICES5", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"IATA", type text}, {"UPDATED", type text}, {"NOTES", type text}}, "en-AU"),{"IATA", "ST", "FUELER", "UPDATED", "RESTRICTIONS", "BASEF UEL", "NOTES"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"GALLONS1", "TOTAL PRICES1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"GALLONS2", "TOTAL PRICES2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.2"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"GALLONS3", "TOTAL PRICES3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.3"),
    #"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"GALLONS4", "TOTAL PRICES4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.4"),
    #"Merged Columns5" = Table.CombineColumns(#"Merged Columns4",{"GALLONS5", "TOTAL PRICES5"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.5"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns5", {"Merged"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}, {"Merged.3", type text}, {"Merged.4", type datetime}, {"Merged.5", type text}, {"Merged.6", type text}, {"Merged.7", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Value.1", "Value.1 - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Value.1 - Copy", "Value.1", "Value.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Reordered Columns", "Value.1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1.1", "Value.1.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Value.1.1", Int64.Type}, {"Value.1.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Merged.1", "IATA"}, {"Merged.2", "ST"}, {"Merged.3", "FUELER"}, {"Merged.4", "UPDATED"}, {"Merged.5", "RESTRICTIONS"}, {"Merged.6", "BASEF UEL"}, {"Merged.7", "NOTES"}, {"Value.1 - Copy", "GALLONS"}, {"Value.1.1", "Min Fuel"}, {"Value.1.2", "Max Fuel"}, {"Value.2", "TOTAL PRICE"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns",{{"UPDATED", type date}})
in
    #"Changed Type4"

Excel - Struggling with complex Index/Match/Match lookup

3 Answers