I have reached the maximum number of Cross References, is there a way to Index Match an Entire row?
I am working on across a couple sheets where the columns contain all the same info across 7-8 sheets and the individual sheets correspond to different groups of people. I am trying to consolidate all of the info into one sheet that updates with changes to the 7-8 reference sheets. I have been doing index matching for each individual column in the consolidated sheet for the 7-8 different groups but have reached the limit on cross referencing. Considering that all of the columns are the same and that they need to be up to date with the information on the 7-8 sheets, I am looking for a solution that would index match the entire row. I am aware that there is the automation that can trigger based on changes to a row, but I have 7-8 sheets that i am trying to transfer data from that all are setup to be in different hierarchies in the consolidated sheet.
Best Answers
-
I think you want to look at using a JOIN(COLLECT()) function which allows you to collect multiple fields at once and join them into a single field. It is possible (with some other formula magic) to then expand that joined field into separate fields on the destination sheet if that is required.
-
My suggestion is to create a string on each of the reference sheets that joins together all of the columns you want to pull over using a delimiter that wouldn't be found in any of the cell data. Then you can INDEX/MATCH this one column over and use formulas to parse it back out from the string. To do this, we will need a second character that would not be found in the cell data.
Create String:
="~" + [Column1]@row + "~" + [Column2]@row + "~" + [Column3]@row + "~"
The above is if your columns you want pulled over are not all right next to each other or could potentially be reordered. If they are all next to each other and will never move in relation to each other, you can use:
="~" + JOIN([Column1]@row:[Column3]@row), "~") + "~"
Then use your INDEX/MATCH to pull over this string column, and use this to parse things out (this is where the second character comes into play / using * in this example):
=MID([String Column]@row, FIND("*", SUBSTITUTE([String Column]@row, "~", "*", 1)) + 1, FIND("*", SUBSTITUTE([String Column]@row , "~", "*", 2)) - (FIND("*", SUBSTITUTE([String Column]@row , "~", "*", 1)) + 1))
The above will pull the first bit of data from the created string. To pull other sets, first notice that there are numbers immediately after each "*" in the above formula. From start to finish, these go 1/2/1. The first number is essentially the entry number to you want to extract, the second number is basically entry number plus one, and the third number is the entry number again. So to pull the second you would use 2/3/2. To pull the tenth: 10/11/10. So on and so forth.
Answers
-
I think you want to look at using a JOIN(COLLECT()) function which allows you to collect multiple fields at once and join them into a single field. It is possible (with some other formula magic) to then expand that joined field into separate fields on the destination sheet if that is required.
-
My suggestion is to create a string on each of the reference sheets that joins together all of the columns you want to pull over using a delimiter that wouldn't be found in any of the cell data. Then you can INDEX/MATCH this one column over and use formulas to parse it back out from the string. To do this, we will need a second character that would not be found in the cell data.
Create String:
="~" + [Column1]@row + "~" + [Column2]@row + "~" + [Column3]@row + "~"
The above is if your columns you want pulled over are not all right next to each other or could potentially be reordered. If they are all next to each other and will never move in relation to each other, you can use:
="~" + JOIN([Column1]@row:[Column3]@row), "~") + "~"
Then use your INDEX/MATCH to pull over this string column, and use this to parse things out (this is where the second character comes into play / using * in this example):
=MID([String Column]@row, FIND("*", SUBSTITUTE([String Column]@row, "~", "*", 1)) + 1, FIND("*", SUBSTITUTE([String Column]@row , "~", "*", 2)) - (FIND("*", SUBSTITUTE([String Column]@row , "~", "*", 1)) + 1))
The above will pull the first bit of data from the created string. To pull other sets, first notice that there are numbers immediately after each "*" in the above formula. From start to finish, these go 1/2/1. The first number is essentially the entry number to you want to extract, the second number is basically entry number plus one, and the third number is the entry number again. So to pull the second you would use 2/3/2. To pull the tenth: 10/11/10. So on and so forth.
-
Thanks! This worked really well!
Help Article Resources
Categories
Check out the Formula Handbook template!