Text Comparison across two sheets to link a multi-select field.

AlexP

Hi everyone,

I am having some difficulty with the following:

I am trying to link/copy/bring-across a multi-select field from one sheet to another based on a Text Field Comparison.

See example below: Lets assume that Primary Column and Result Column are on Sheet 1 and Column 3 and Column 4 are on Sheet 2.

User populates Sheet 2 such that column 3 has text and Column 4 contains items (Primary Column and Column 3 will have the same text but not necessarily in the same order). I am then trying to bring whatever is in column 4 into the Result column (Which is on a different sheet). However, some kind of sorting/comparison needs to take place in order to get the items into the correct rows.

For instance - Result column should have the following logic (however not sure on the correct way to do this)

=IF([Primary Column]@row={Column3}@row, then true value should be whatever is in Column 4, false value I'm not sure on).

Any assistance on this matter is much appreciated.

Let me know if further information is required.

Thanks.

Mark Cronk

Hi @Alex P ,

It looks like you can accomplish this with a vlookup or index-match as long as the values in column 3 are unique.

Your formula in the Results column would be along the lines of:

=VLOOKUP([primary column]@row, {insert range = to column 3 and 4}, 2, False)

Work?

Mark

AlexP

Hi @Mark Cronk

I don't quite follow the {insert range = to column 3 and 4} portion of that formula. Would you mind explaining that a little more?

Thanks.

Mark Cronk

Hi Alex,

That's a place holder for the cross-sheet reference that you'll need to create. You need to use a cross-sheet reference because you said your columns 3 and 4 were in another sheet. Cross-sheet references appear in "{ }" brackets.

To create the cross-sheet range, start entering the formula into your [results] column. The formula help will appear and you'll see a blue highlighted link "Reference Another Sheet". When you get to the placeholder portion, click the Reference link and it will open a dialog box. Select the sheet with your other data and then highlight the columns 3 and 4. Give it a name in the "Sheet Reference Name" box and select "Insert Reference". You'll return to your formula and the range you named should appear. Complete the formula and see if it works.

Here's a video tutorial on Cross-Sheet references: https://www.youtube.com/watch?v=u01GHwmlDRA

Let me know if you need more assistance. Happy to help.

Mark

Paul Newcome

I agree that a VLOOKUP or (my personal preference) an INDEX/MATCH would be the way to go.

@Mark Cronk has already provided the VLOOKUP.

The INDEX/MATCH would look something like this...

=INDEX({Other Sheet Column 4 Reference}, MATCH([Primary Column]@row, {Other Sheet Column 3 Reference}, 0))

I just like the INDEX/MATCH a little better because it is much more flexible to adjustments in the source data layout.

AlexP

Hi @Mark Cronk and @Paul Newcome ,

Thank you to you both for your insightful replies.

Mark, I didn't realise you could highlight more than one column using that method. So thank you - I'll keep that in mind.

I did however opt for the INDEX/MATCH method and this worked perfectly.

Thanks again.

Paul Newcome

Happy to help. 👍️