index match with a set of data (2 dimensional match)

jimmbo · 09/08/23

I am trying to use index match but my match is using a range of data (not just 1 column, but many). This causes the match function to return a 2d number relative to the row/column matched and not just a row number. It seems index is expecting just a row number (and optional 3rd parameter for column) and this does not work together.

How can I get index match to work when match is returning a 2-dimensional result to the index function?

Thank you.

jimmbo · 09/08/23

So I guess there are 2 problems with what I am trying to do.

It does not appear that you can use index when using match within a range, only a single column. Match seems to have no trouble returning a value corresponding to the matched row/column pair in the range specified, but since index only expects a row number for its 2nd parameter, this does not work together. Having to create a separate sheet reference for each column I want to match is kind of annoying. Why can't you just pass a single value to index that is the row/column number corresponding to the location in a range?
In order to have a dynamic column (as the 3rd parameter to the index function), I would need to type the column names in a row in the source sheet so I could reference them with match as you cannot match on the column names themselves. This does seem to be a shortcoming of SS currently.

Well anyway, I will make the needed changes to my document but if anyone would like to add anything to this topic, feel free.

Thanks.

Paul Newcome · 09/11/23

You don't have to type in column names to be able to match on them. If you already know the logic that determines which of the two columns to pull from, you can use an IF statement in the 3rd portion of the INDEX function.

Are you able to provide some screenshots for context?

jimmbo · 09/11/23

https://community.smartsheet.com/discussion/comment/394739#Comment_394739

I am attempting to look up the date in the source sheet and enter it into the target sheet (for the corresponding Opportunity ID).

On the Target Sheet date I am using "=INDEX({Source Sheet whole sheet range}, MATCH([Opportunity ID]@row, {Source Sheet OppID}, 0), 1)"

This works, but it's not ideal for 2 reasons.

I have to hardcode the column I want to return (the ending "1" parameter to the index command).
I have to create a new sheet reference for each column I want to match on ("Source Sheet OppID" for example).

I would like a method that does not break when columns are moved, and I would really like to be able to use 1 single sheet reference (Source Sheet whole sheet range) and be able to just specify which column to return (by name, not number). There are many columns that I would like to pull from the source sheet so this gets tedious.

Thanks again.

Paul Newcome · 09/12/23

Ok. I understand now. What you are looking for isn't possible

The flexibility from an INDEX/MATCH comes from creating those different cross sheet references. Manually typing in a column name and changing it for each formula isn't much different than creating separate cross sheet references. It is just keystrokes instead of clicks.

The challenge that would arise from being able to just type in a column name would be if that column name is changed. If we could more easily access the column ID and use that instead, it might work a little better.

My suggestion (which doesn't provide a solution but is the best we can do) is to browse the Product Ideas tab (at the top of this page) to see if someone else has already submitted the idea. If they have, you can add your vote. If no one has, you can submit it yourself.

Either way, please provide a link to the Idea here in this thread so that others searching for the same thing can more easily add their votes.

jimmbo · 09/13/23

I thought that was the case, but I appreciate you confirming. What I really don't understand is why Index cannot just accept a 2 dimensional result from Match as a single parameter and be done with it. If it could, this whole thing would be much easier and much more flexible as you would not even need to define a column at all. Just provide a search value and a range (even the whole sheet) and it would return the R/C coordinates. Can't get much better than that.

You mentioned that the flexibility from index/match comes from creating those different cross sheet references, but I do not see it that way. This requirement essentially makes it no more flexible than vlookup.

Paul Newcome · 09/13/23

The flexibility from creating individual cross sheet references means you can reorder the columns in the source sheet as much as you want. As long as you don't delete the columns, they can go wherever you want them to in the source sheet to include the column to match on does not have to be the rightmost column in your table.

Want to pull the far left column and match on a hidden column on the far right of the sheet? You can do that with individual cross sheet references but not a VLOOKUP.

Want to add a few columns in between these two referenced columns so now your sheet has 54 columns in between the two references instead of the original 40? Not going to break anything unless you are using VLOOKUP.

Want to rearrange the columns on your source sheet? You can do that too with the individual cross sheet references.

If you hard-code a column number to pull from (as in VLOOKUP) and that column moves within the source sheet, now your hardcoded number is no longer the correct number.

Not to mention the processing power required when your source data has a lot of columns in between the one to match on and the one to pull.

VLOOKUP with a single cross sheet reference pulling from a table 54 columns wide has to reference not just the columns you are matching and pulling but every column in between. That could be A LOT of cells being referenced.

The equivalent INDEX/MATCH with individual columns being cross sheet referenced only references those two columns and completely ignores everything in between.

The flexibility I am referring to is the fact that with individual cross sheet references, you do not have to hard code a column number, and you do not have to worry about anything breaking if the structure of the source data changes (unless the change is a referenced column being deleted).

index match with a set of data (2 dimensional match)

Answers

Help Article Resources

Categories