Advanced Lookup Help

[ryansm05]

Hi all,

I have a pretty complex look-up issue and wondered if anyone can help? The link to the G-sheet and criteria are below (also within the G-sheet):

Link:

Fixtures

Target Week Team,1,2,3 1,2 (A),10 (H),3 (A) 2,1 (H),9 (A),4 (A) 3,4 (A),8 (H),1 (H) 4,3 (H),7 (A),2 (H), 5 (A) 5,6 (A),6 (H),7 (A), 4 (H) 6,5 (H),5 (A),8 (A) 7,8 (A),4 (H),5 (H) 8,7 (H),3 (A),6 (H) 9,10 (A),2 (H),10 (A) 10,9 (H),1 (A),9 (H) Criteria 1) Look down column B in DATA TAB and match w...

docs.google.com

Criteria
1) Look down column B in DATA TAB and match with row 3 in TARGET TAB
2) Look down column C AND D in DATA TAB and return result that matches with TEAM in column B in TARGET TAB
3) If match is found in column C DATA TAB then add "(A)" to result
4) If result is found in column D DATA TAB then add "(H)" to result
5) if there are multiple matches, then separate these with a comma (see E7 & E8 in TARGET TAB)
6) if there are duplicate entries (row 5 & 15 in DATA TAB) then ignore

If anyone could help it would be massively appreciated.

Happy for this to be solved in Excel.

Thanks in advance
Ryan

 

[jasonb75]

Do you need this to work in Excel or Google sheets?

Common methods for complex lookups in excel will not work with google sheets.

 

[ryansm05]

Hi Jason,

Preferably in Google Sheets ... but if that's not possible, it'd still be very helpful to solve this in Excel.

Cheers
Ryan

 

[jasonb75]

Using the dynamic array functions which require an up to date version of office 365 (Excel). This may be translatable to google sheets, I'm not familiar with the functions that it has available.

For older versions of excel, I don't see that it would be possible with a formula, likewise for google sheets if it does not have the functions used or an extremely close equivalent.

Note that the cell in yellow contains the correct results, but the order is slightly different. The formula reads by column rather than by row which you appear to have done, I don't see any way that it can be done in the same order as your example.

Book2
	B	C	D	E
2		Week
3	Team	1	2	3
4	1	2 (A)	10 (H)	3 (A)
5	2	1 (H)	9 (A)	4 (A)
6	3	4 (A)	8 (H)	1 (H)
7	4	3 (H)	7 (A)	2 (H), 5 (A)
8	5	6 (A)	6 (H)	4 (H), 7 (A)
9	6	5 (H)	5 (A)	8 (A)
10	7	8 (A)	4 (H)	5 (H)
11	8	7 (H)	3 (A)	6 (H)
12	9	10 (A)	2 (H)	10 (A)
13	10	9 (H)	1 (A)	9 (H)
Target

Cell Formulas
Range	Formula
C4:E13	C4	=LET(h,UNIQUE(FILTER(Data!$C$4:$C$21,(Data!$B$4:$B$21=C$3)*(Data!$D$4:$D$21=$B4),"")),a,UNIQUE(FILTER(Data!$D$4:$D$21,(Data!$B$4:$B$21=C$3)*(Data!$C$4:$C$21=$B4),"")),TEXTJOIN(", ",1,IF(h="","",h&" (H)"),IF(a="","",a&" (A)")))

 

[ryansm05]

Thanks for your suggestion Jason.

There is an insane amount of knowledge within that formula - but unfortunately for me, it did not work on either Excel nor Google Sheets.

I'm going to have a re-think about how I can do this differently but will mark the thread as a solution solved.

Regards
Ryan

 

[jasonb75]

If you update your profile to show which version of excel you're using then I'll have another look at it for you (click your user name at the top right, then Account Details. Scroll down and tick the version box, then save changes at the bottom).

I haven't tried alternatives yet, but at a glance I think something might be possible although multiple entries and duplicates are looking like some big hurdles.
In the case of multiple matches, would it be a maximum of 2? If not what would be the highest?