Google Sheets match multiple results

0
votes

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

3
google-sheets
Your sample spreadsheet gives "You need permission." To best get help, please share your sample spreadsheet with "can edit" rights. - JPV
thanks @JPV I have an answer but need to add an "if empty" element or the formula assumes that all possible fruits fit the query - Colin Webber

3 Answers

1
votes

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)

0
votes

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

0
votes

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.