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 repeat in the chart.
For example ANA-D60FC is my identifier, but it can be followed by different information.
| 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 |
| 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 |
| 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 |
I need to pull the column information by matching the identifier from chart 1 to chart 2, but I need every instance where it is different.
Right now I am using a double Xlookup function but it still just gives me the same information from the first matching column It finds and duplicates that information even if other matching identifying values have different information.
Here's the current formula I thought would work by finding the identifer then finding the column next to it and pulling that columns info, but it just pulls the same info and duplicates.
=XLOOKUP($B10,Table6[Atta3 ID],XLOOKUP(Table5[[#Headers],[DeptName]],Table6[[#Headers],[DeptName]:[ListPrice]],Table6[[DeptName]:[ListPrice]],"F",0))
It's beyond my knowledge but a formula that works on this could be helpful for many areas across my job.
7 Replies
- 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 INDEXI 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
