CODEWITHSUNDEEP
google-sheets
Colin Webber
Colin Webber

Reputation: 1

Google Sheets match multiple results

I have a list of codes I need to match to cross reference across 2 sheets in a workbook. Each entry can have multiple matches and the columns are fixed, because there is other data in between.

I have an example sheet here.

which looks like this Cross referencing between sheets "Sheet2" is the source, Sheet 1 Column F is the desired result This has gone beyond my VLOOKUP skills

Thanks in advance

Upvotes: 0

Views: 4295

Answers (3)

JPV
JPV

Reputation: 27262

this would be my solution..

=if(LEN(A2), JOIN(", ", FILTER('VET Module Codes'!$B:$B, regexmatch('VET Module Codes'!$D:$D, A2))),)

and filled down. This checks if there is a value in col A, before the lookup is done.

Upvotes: 0

Colin Webber
Colin Webber

Reputation: 1

The full answer to this problem looks like this

= (IFERROR(IF(ISBLANK(A2),"",JOIN(", ", FILTER('Named Sheet A'!$B:$B, FIND(A2, 'Named Sheet B'!$D:$D))))))

Part of my problem was in trying to use IFERROR on its own, and using an excel code ISEMPTY instead of ISBLANK

Upvotes: 0

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

Given the data structure in your screenshot:

=JOIN(", ", FILTER($A$10:$A$14, FIND(A2, $C$10:$C$14)))

What's happening here is:
In F2 we try to find the fruit in A1 in the lists of Fruits the individuals like (The formula by itself only works as an array formula):

=FIND(A2, $C$10:$C$14)

The result of that is a list of numbers indicating the position of the matched fruits and errors.
In a boolean interpretation Errors are FALSE and numbers other than 0 are TRUE.
We use this property to filter the range of the names:

=FILTER($A$10:$A$14, FIND(A2, $C$10:$C$14))

Lastly we join the range together, with a comma and a space (See above)

Upvotes: 1

Related Questions