Forum Discussion
Double Xlookup not working for what I need
So I am trying to pull information from two charts that have a matching identifying value, however the information in the columns following that unique value can be different and these values can rep...
Okay great! Thanks!
Now the hard part for me. I have two arrays. Arrays 1 & 2 have similar formulated data. However, array 2 has three times as much data. I need to know how much similar data both arrays have (row for row). I need to create a third array made up of all the rows that both arrays 1 & 2 have in common. The rows have to match exactly, not just the first column of identifiers since columns 2 and 3 can change.
That is where I have been failing. I can't find a way to insert the unique function into a Index-Match or Xlookup function.
Now the hard part for me. I have two arrays. Arrays 1 & 2 have similar formulated data. However, array 2 has three times as much data. I need to know how much similar data both arrays have (row for row). I need to create a third array made up of all the rows that both arrays 1 & 2 have in common. The rows have to match exactly, not just the first column of identifiers since columns 2 and 3 can change.
That is where I have been failing. I can't find a way to insert the unique function into a Index-Match or Xlookup function.
JCBenny Try this
=LET(UniqSets,VSTACK(UNIQUE(Table1),UNIQUE(Table2)),
AllUniq, UNIQUE(UniqSets),
NonOverlap, UNIQUE(UniqSets,,TRUE),
out,IFERROR(UNIQUE(VSTACK(AllUniq,NonOverlap),,TRUE),"none"),
out)
If you replace "out" on line 5 with "AllUniq" or "NonOverlap" you can see more of what is happening. But this same formula works on the attached. If you send me a copy of the actual sheet (no private info) I might help more.