Match two columns to multiple other columns, and return another column.

[stranula]

I'm not sure how to best describe my problem, but it is somewhat an Index(Match) or XLookup problem. I am able to partially solve it with those two options, but need to get a little more figured out.

The starting data is formatted as such:

CD16	eFluor 660
CD7	Vio667
CD4	Alexa Fluor 647

And the goal is to compare these two columns to a similar list and find replacements for the second column, based on the first.

The comparison table is formatted such as:

CD4	C 668	Alexa Fluor 647	eFluor 660	Vio667
CD16	C 668	Alexa Fluor 647	eFluor 660	Vio667
CD7	C 668	Alexa Fluor 647	eFluor 660	Vio667

And what I need is to search that table to see if there is any row that matches both criteria of the first set to the second set, then return C 668. So the correct answer for all of those is C 668.

Using Index(Match) or XLookup(), I can get the correct result using just the 3rd column of the second dataset, but I need it to be able to search all columns.

I've attached a file that has some realistic data in it, expanded beyond what is outlined here.

Any help would be greatly appreciated.

Thanks

[alansidman]

Since you have O365, this can be accomplished using Power Query called Get and Transform Data found on the Data tab of your excel version.

1. Load each table to PQ editor
2. Unpivot the table on the Replace From Tab for all columns except the first two
3. Join (Merge the two tables based upon the matching columns.

Result shown below.

Excel 2016 (Windows) 32 bit

	E	F	G
1	CCR7 (CD197)	BV421	Table1.Fluor
2	CD16	eFluor 660	C 668
3	CD4	Alexa Fluor 647	C 668
4	CD7	Vio667	C 668

Sheet: Find a Replacement

Mcode to show merge (join)

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

file attached so you can review all steps.

[Paul]

One of the difficulties here is that you have multiple instances of your lookup values in the Marker column ('Replace from'!A:A). This makes using LOOKUP or INDEX/MATCH functions difficult because they typically only find the first match and then search left/right from there. I'd highly suggest re-organizing your data to have a unique list in three columns, e.g. Marker, SearchFor, ReplaceWith. You would then be able to search using something like =INDEX('Replace from'!$C:$C,MATCH(A2&B2,'Replace from'!$A:$A&'Replace from'!$B:$B,0)).

Short of doing that (which might be tedious if you have a LOT of rows), I tried this:

On the 'Replace from' sheet, in L2 put the formula: =$A2&C2

Fill that across to T2 (since you have 9 columns of search terms in C:K), then fill down as many rows as needed. This should create unique values to search for in a range of cells, e.g. "CD4PE-eFluor 610", "CD4PE-Texas Red", etc.

Then in D1 on the 'Find a Replacement' sheet, use the array formula (confirmed using CTRL+SHIFT+ENTER, not just ENTER):

Please Login or Register  to view this content.

Fill that down the column as far as necessary.

This formula first checks to see if the "Marker" value is found in the first column of the 'Replace from' sheet. If not, it returns "No Match". Otherwise, it should return the value from 'Replace from' column B in the same row that the combined values from columns A&B (on 'Find a Replacement' sheet) are found.

Hope that works!

[stranula]

Hi Paul and Alansidman,

Thank you for taking the time to look over my question and provide thoughtful answers. Paul, unfortunately I cannot change the whole dataset easily without drastically increasing the size of my table. But I will use that as a second option.

Alansidman, I think you've put me on the right path and I will give that a shot.

Thanks again