Formula returns blank due to duplicates

[ozbeachbum]

I am using the following formula, however where ever there is duplicate text or a text string containing duplicate non alpha characters EG: [ ] the formula only returns a result for one and a blank for the other.

=IF(GD70:GD340="","",XLOOKUP(GD70:GD340,JN70:JN340,JP70:JP340,"",0)) [Column JP contains the targeted information]

I had a similar issue with another formula which Peter solved for me by using INDEX, I tried using it with this formula but not fully understanding the context I was not successful.
Any assistance greatly appreciated.

 

[hagia_sofia]

Could you possibly upload a sample of your data because from the description it seems that there is no match for the relevant data, therefore e.g. use of of wildcards is needed...

 

[ozbeachbum]

Hi,
Thanks for the reply.
I have provided a copy of the section of the sheet with the problem, adulterated as there were too many cells for a mini sheet.
Thanks for you help.

240818 duplicates.xlsx
	GD	GE	GF	GG	GH	GI	GJ	GK	GL
70	Bogangar Body Corp:		00.00		Bogangar Body Corp:				DE
71
72	NIB:		00.00		NIB:				DE
73
74	Woolworths:		00.00		Woolworths:				DE
75
76	Repay Monies Borrowed RES02 Geriatric Gypsies World Tour:		00.00		Repay Monies Borrowed RES02 Geriatric Gypsies World Tour:				DE
77
78	(Membership Fees) Lions:		00.00		(Membership Fees) Lions:				DE
79
80	(Dinners Etc) Lions:		00.00		(Dinners Etc) Lions:				DE
81
82	Grandkids 18th:		00.00		Grandkids 18th:				DE
83
84	Expenses Redraw Average:		00.00		Expenses Redraw Average:				DE
85
86	Mortgage Interest:		00.00		Mortgage Payment Fortnightly Minimum:
87
88	RES02 Body Corporation:		00.00		Mortgage Payment Fortnightly Minimum:
89
90	RES02 Hollard Insurance:		00.00		Mortgage Payment Fortnightly Minimum:
91
92	Gold Coast City Council [Rates Property]:		00.00		Mortgage Interest:				DE
93
94	Gold Coast City Council [Rates Water]:		00.00		RES02 Body Corporation:				DE
95
96	Tweed Shire Council [Rates Property]:		00.00		RES02 Hollard Insurance:				DE
97
98	Tweed Shire Council [Rates Water]:		00.00		Gold Coast City Council [Rates Property]:				DE
99
100	Dodo Power & Gas:		00.00		Gold Coast City Council [Rates Water]:				DE
101
102	Optus Mobile:		00.00		Tweed Shire Council [Rates Property]:				DE
103
104	AAA Comet Gas:		00.00		Tweed Shire Council [Rates Water]:				DE
105
106	Cudgen Headland SLSC:		00.00		Dodo Power & Gas:				DE
107
108	Cabarita Beach Bowls & Sports Club:		00.00		Optus Mobile:				DE
109
110	Cabarita Beach Bogangar Residents Association:		00.00		AAA Comet Gas:				DE
111
112	Pottsville Beach Sports Club:		00.00		Cudgen Headland SLSC:				DE
113
114	Australian Atheists Society:		00.00		Cabarita Beach Bowls & Sports Club:				DE
115
116	(Safety Check) Mazda 2 Pink Slip:		00.00		Cabarita Beach Bogangar Residents Association:				DE
117
118	(Insurance Compulsory Third Party) Mazda 2 Green Slip:		00.00		Pottsville Beach Sports Club:				DE
119
120	(Insurance Third Party Property Damage) Mazda 2 NRMA:		00.00		Australian Atheists Society:				DE
121
122	Blank Bottom		00.00		(Safety Check) Mazda 2 Pink Slip:				DE
123
124	Blank Row		00.00		(Insurance Compulsory Third Party) Mazda 2 Green Slip:				DE
125
126	Debits Transferred from TR Mmm & Mmm		00.00		(Insurance Third Party Property Damage) Mazda 2 NRMA:				DE
127
128	Blank Row		00.00		Blank Bottom				DE
129					Blank Row				DE
130	Blank Top		00.00		Debits Transferred from TR Mmm & Mmm				DE
131					Blank Row				DE
132	Housekeeping:		00.00		Blank Top				DE
133
134	Medical & Medications:		00.00		Housekeeping:				DE
135
136	Household Items:		00.00		Medical & Medications:				DE
137
138	Personal:		00.00		Household Items:				DE
139
140	Entertainment:		00.00		Personal:				DE
141
142	Gifts & Raffles:		00.00		Entertainment:				DE
143
144	Vehicle:		00.00		Gifts & Raffles:				DE
145
146	Miscellaneous:		00.00		Vehicle:				DE
147
148	Holidays & Sydney Trip:		00.00		Miscellaneous:				DE
149
150	RES02 CA&W; R&M Payments by Us:		00.00		Investments:
151
152	Transfers:		00.00		Holidays & Sydney Trip:				DE
153
154	Blank Bottom		00.00		RES02 CA&W; R&M Payments by Us:				DE
155
156	Blank Row		00.00		Transfers:				DE
157						Redraw Disinvestments:
158	Southport		00.00		Blank Bottom	Redraw Disinvestments:			DE
159					Blank Row				DE
160	Blank Row		00.00		Southport				DE
161					Blank Row	Redraw Extras:			DE
162	Expenditure RES02		00.00		Expenditure RES02				DE
163					Blank Row	Redraw Capital Allocations & Works; Maintenance & Repairs:			DE
164	Blank Row		00.00		Blank Top				DE
165						Rent Advanced Payments:
166	Blank Top		00.00		Grand Totals:				DE
167						Rent Advanced Payments:
168	Grand Totals:		00.00		Total Advertising:		01		DE
169
170	Total Advertising:		01.00		RES02 Body Corporation:		02		DE
171
172	RES02 Body Corporation:		00.00		Deductable Borrowing Expenses:		03		DE
173
174	Deductable Borrowing Expenses:		03.00		Total Cleaning Charges:		04		DE
175
176	Total Cleaning Charges:		04.00		Gold Coast City Council [Rates Property]:		05		DE
177
178	Gold Coast City Council [Rates Property]:		00.00		Capital Allowances:		06		DE
179
180	Capital Allowances:		06.00		Gardening:		07		DE
181
182	Gardening:		07.00		RES02 Hollard Insurance:		08		DE
183
184	RES02 Hollard Insurance:		00.00		Mortgage Interest:		09		DE
185
186	Mortgage Interest:		00.00		Land Tax:		10		DE
187
188	Land Tax:		10.00		Legal Fees:		11		DE
189
190	Legal Fees:		11.00		Pest Control:		12		DE
191
192	Pest Control:		12.00		(Administration Fee `+` Rent Commission `+` Letting Fee ) Agent's Fees:		13		DE
193
194	(Administration Fee `+` Rent Commission `+` Letting Fee ) Agent's Fees:		13.00		Agent Administration Fee:		13.01		DE
195
196	Agent Administration Fee:		13.01		Rent Commission $:		13.02		DE
197
198	Rent Commission $:		13.02		(Lease inc GST) Agent Letting Fee:		13.03		DE
199
200	(Lease inc GST) Agent Letting Fee:		13.03		Maintenance & Repairs:		14		DE
201
202	Maintenance & Repairs:		14.00		Total Capital Works:		15		DE
203
204	Total Capital Works:		15.00		Phone; Postage & Stationery:		16		DE
205
206	Phone; Postage & Stationery:		16.00		Travel Expenses:		17		DE
207
208	Travel Expenses:		17.00		Gold Coast City Council [Rates Water]:		18		DE
209
210	Gold Coast City Council [Rates Water]:		00.00		Total Other Expenses:		19		DE
211
212	Total Other Expenses:		19.00		Blank Bottom				DE
213					Blank Row				DE
214	Blank Bottom		0
215
216	Blank Row		0
217
Blank (2)

Cell Formulas
Range	Formula
GD70:GD217	GD70	=LET(f,FILTER(GH70:GH340,(GL70:GL340="DE")*(GH70:GH340<>"")),s,SEQUENCE(2*ROWS(f),,2),IF(ISEVEN(s),INDEX(f,s/2),""))
GF70:GF340	GF70	=IF(GD70:GD340="","",XLOOKUP(GD70:GD340,GH70:GH340,GJ70:GJ340,"",0))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
GL70:GL213	Cell Value	=1	text	YES
GL70:GL213	Cell Value	="1M"	text	YES
GL70:GL213	Cell Value	=2	text	YES
GL70:GL213	Cell Value	="2M"	text	YES
GL70:GL213	Cell Value	=3	text	YES
GL70:GL213	Cell Value	="3M"	text	YES
GL70:GL213	Cell Value	=4	text	YES
GL70:GL213	Cell Value	=5	text	YES
GL70:GL213	Cell Value	="5M"	text	YES
GL70:GL213	Cell Value	=6	text	YES
GL70:GL213	Cell Value	=7	text	YES
GL70:GL213	Cell Value	="7M"	text	YES
GL70:GL213	Cell Value	contains "CE"	text	YES
GL70:GL213	Expression	=AND(GL70="DE",GL71="DE")	text	YES
GL70:GL213	Cell Value	="DE"	text	YES

 

[Peter_SSs]

What is the formula supposed to be doing?

Currently it is looking for the first occurrence of the GD value in GH and returning the value from GJ. So for row 172 for example it is looking for "RES02 Body Corporation:" and finds it in GH94 so returns the value from GJ94 which is blank so returns it as 0.

 

[ozbeachbum]

I am trying to have the formula return the number in GJ if GD & GH match

EG:
"Total Advertising:" GD170 = GH168 Should Return 01 from GJ168 (Current sheet shows 01)
"RES02 Body Corporation:" GD172 = GH170 Should Return 02 from GJ170 (Current sheet shows 00)
"Mortgage Interest" GD186 = GH184 Should Return 09 from GJ184 (Current sheet shows 00)

Trust this helps.
Thanks again for all your assistance.

 

[Peter_SSs]

Does this do what you want then?

=IF(GD70:GD340="","",SUMIFS(GJ70:GJ340,GH70:GH340,GD70:GD340))

 

[ozbeachbum]

Hi Peter,
Can't thank you enough, works perfectly.

 

[Peter_SSs]

Cheers. Glad to help.