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...
I think we need more information to figure this out. You say you have this formula:
=XLOOKUP($B10,Table6[Atta3 ID],XLOOKUP(Table5[[#Headers],[DeptName]],Table6[[#Headers],[DeptName]:[ListPrice]],Table6[[DeptName]:[ListPrice]],"F",0))
but we don't know what $B10 points at and don't know what Table6 and Table5 look like. You included a sample table but not sure which of these that is a sample of.
As for the formula it looks like you are trying to return a value from the [DeptName] column from Table6 if $B10 matches [Atta3 ID]. But that would return 1 value. It sounds like you want all the unique values? Maybe something more like:
=UNIQUE(FILTER(Table6[[DeptName]:[ListPrice]], Table6[Atta3 ID]=$B10))
and then if you only want a certain column then maybe wrap that with CHOOSECOLS or INDEX
=XLOOKUP($B10,Table6[Atta3 ID],XLOOKUP(Table5[[#Headers],[DeptName]],Table6[[#Headers],[DeptName]:[ListPrice]],Table6[[DeptName]:[ListPrice]],"F",0))
but we don't know what $B10 points at and don't know what Table6 and Table5 look like. You included a sample table but not sure which of these that is a sample of.
As for the formula it looks like you are trying to return a value from the [DeptName] column from Table6 if $B10 matches [Atta3 ID]. But that would return 1 value. It sounds like you want all the unique values? Maybe something more like:
=UNIQUE(FILTER(Table6[[DeptName]:[ListPrice]], Table6[Atta3 ID]=$B10))
and then if you only want a certain column then maybe wrap that with CHOOSECOLS or INDEX
I really appreciate you looking into this for me. You are right, I was rushing to explain this and did a pretty bad job so let me simplify what I really need.
Below is an example of the data I'm using. I work in the medical industry and have thousands of these rows for just one project.
As you can see, something like "ANA-C069B" is used multiple times but in different ways. I need to pull each distinct/unique way it is listed, row by row, without pulling any duplicates. I need to do that with every one of the IDs not just "ANA-CO69B" throughout my thousands of rows. That is my main problem here.
Any help is greatly appreciated!
ANA-C069B 06 - Intermediate Care / ICU Soiled Workroom Analyzer, Lab: Urinalysis, Semi-Automated Siemens Healthcare Diagnostics Clinitek Status + $1,200.00 ANA-C069B 06 - Intermediate Care / ICU Soiled Workroom Analyzer, Lab: Urinalysis, Semi-Automated Siemens Healthcare Diagnostics Clinitek Status + $1,200.00 ANA-C069B 07 - Med/Surg Soiled Workroom Analyzer, Lab: Urinalysis, Semi-Automated Siemens Healthcare Diagnostics Clinitek Status + $1,200.00 ANA-C069B 07 - Med/Surg Soiled Workroom Analyzer, Lab: Urinalysis, Semi-Automated Siemens Healthcare Diagnostics Clinitek Status + $1,200.00 ANA-D36C8 02 - Lab Chemistry Analyzer, Lab: Chemistry Roche Diagnostics Corporation Cobas Pure C 303 Analytical Unit $38,870.00 ANA-D39A0 02 - Lab Coagulation Analyzer, Lab: Coagulation, Plasma Werfen ACL TOP 350 CTS $105,000.00 ANA-D60FC 02 - PACU (12 Bay) Nurse Station (with Nourishment & Team Work) Analyzer, Lab: Glucose, Point-of-Care Nova Biomedical Nova StatStrip Glucose Hospital Meter w/1D Barc $700.00 ANA-D60FC 02 - Pre-Op (Day Surgery) Nurse/Central Station Analyzer, Lab: Glucose, Point-of-Care Nova Biomedical Nova StatStrip Glucose Hospital Meter w/1D Barc $700.00 ANA-D60FC 02 - Pre-Op (Day Surgery) Nurse/Central Station Analyzer, Lab: Glucose, Point-of-Care Nova Biomedical Nova StatStrip Glucose Hospital Meter w/1D Barc $700.00 ANA-D60FC 02 - Pre-Op (Day Surgery) Nurse/Central Station Analyzer, Lab: Glucose, Point-of-Care Nova Biomedical Nova StatStrip Glucose Hospital Meter w/1D Barc $700.00 ANA-D60FC 03 - Ante/Post Partum Meds (Post Partum) Analyzer, Lab: Glucose, Point-of-Care Nova Biomedical Nova StatStrip Glucose Hospital Meter w/1D Barc $700.00 JCBenny Very simply =UNIQUE(A1:G10000) should do that for you. You can also use DATA -> Remove Duplicates if you just want to remove those duplicate rows
- 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.
