Fuzzy Lookup Returning Additional Results

andybason

Board Regular
Joined
Jan 7, 2012
Messages
217
Office Version
  1. 2016
Hello

I am trying to write a formula to lookup "Hamilton" in a range. The town is either referred to in full or just as "Ham".

The formula that I have come up with returns the desired results but it also returns other towns such as Wolverhampton and Cheltenham which contain the word "ham".

This is the formula and an extract of the range is below. Is there a way of tweaking it to only return the desired results?

=COUNTIF(UKTable[TOWN],"*Ham*")

Thank you

Book1
A
1TOWN
2GB / Ham 3rd May
3GB / Ham 15th May
4GB / Ham 27th May
5GB / Ham 4th Jun
6GB / Ham 10th Jun
7GB / Ham 17th Jun
8GB / Ham 25th Jun
9GB / Ham 30th Jun
10GB / Ham 11th Jul
11GB / Ham 16th Jul
12GB / Ham 17th Jul
13GB / Ham 1st Aug
14GB / Ham 20th Aug
15GB / Ham 28th Aug
16GB / Ham 1st Sep
17GB / Ham 20th Sep
18GB / Ham 21st Sep
19GB / Ham 28th Sep
20GB / Ham 1st May
21GB / Ham 13th May
22GB / Ham 25th May
23GB / Ham 2nd Jun
24GB / Ham 8th Jun
25GB / Ham 15th Jun
26GB / Ham 23rd Jun
27GB / Ham 28th Jun
28GB / Ham 9th Jul
29GB / Ham 14th Jul
30GB / Ham 15th Jul
31GB / Ham 30th Jul
32GB / Ham 18th Aug
33GB / Ham 26th Aug
34GB / Ham 30th Aug
35GB / Ham 19th Sep
36GB / Ham 24th Sep
37GB / Ham 26th Sep
38GB / Ham 7th May
39GB / Ham 19th May
40GB / Ham 1st Jun
41GB / Ham 7th Jun
42GB / Ham 14th Jun
43GB / Ham 21st Jun
44GB / Ham 29th Jun
45GB / Ham 3rd Jul
46GB / Ham 4th Jul
47GB / Ham 20th Jul
48GB / Ham 21st Jul
49GB / Ham 5th Aug
50GB / Ham 24th Aug
51GB / Ham 25th Aug
52GB / Ham 2nd Sep
53GB / Ham 5th Sep
54GB / Ham 6th May
55GB / Ham 18th May
56GB / Ham 31st May
57GB / Ham 6th Jun
58GB / Ham 13th Jun
59GB / Ham 20th Jun
60GB / Ham 28th Jun
61GB / Ham 2nd Jul
62GB / Ham 3rd Jul
63GB / Ham 14th Jul
64GB / Ham 19th Jul
65GB / Ham 4th Aug
66GB / Ham 21st Aug
67GB / Ham 31st Aug
68GB / Ham 13th Sep
69GB / Ham 23rd Sep
70GB / Ham 24th Sep
71GB / Ham 5th May
72GB / Ham 17th May
73GB / Ham 29th May
74GB / Ham 12th Jun
75GB / Ham 19th Jun
76GB / Ham 27th Jun
77GB / Ham 13th Jul
78GB / Ham 18th Jul
79GB / Ham 3rd Aug
80GB / Ham 30th Aug
81GB / Ham 22nd Sep
82GB / Ham 30th Sep
83GB / Ham 24th Jun
84GB / Ham 12th Jul
85GB / Ham 16th Jul
86GB / Ham 1st Aug
87GB / Ham 13th Aug
88GB / Ham 1st Sep
89GB / Ham 20th Sep
90GB / Ham 21st Sep
91GB / Ham 28th Sep
92GB / Wolverhampton 8th Mar
93GB / Wolverhampton 12th Mar
94GB / Wolverhampton 13th Mar
95GB / Wolverhampton 15th Mar
96GB / Cheltenham 16th Mar
97GB / Cheltenham 17th Mar
98GB / Cheltenham 18th Mar
99GB / Hexham 18th Mar
100GB / Cheltenham 19th Mar
101GB / Fakenham 19th Mar
102GB / Wolverhampton 20th Mar
103GB / Wolverhampton 27th Mar
104GB / Wolverhampton 29th Mar
105GB / Fakenham 30th Mar
106GB / Wolverhampton 30th Mar
107GB / Hexham 31st Mar
108GB / Wolverhampton 3rd Apr
109GB / Wolverhampton 5th Apr
110GB / Nottingham 7th Apr
111GB / Wolverhampton 10th Apr
112GB / Wolverhampton 12th Apr
113GB / Cheltenham 14th Apr
114GB / Cheltenham 15th Apr
115GB / Nottingham 17th Apr
116GB / Hexham 19th Apr
117GB / Wolverhampton 20th Apr
118GB / Wolverhampton 24th Apr
119GB / Nottingham 27th Apr
120GB / Wolverhampton 28th Apr
121GB / Cheltenham 30th Apr
122GB / Hexham 1st May
123GB / Hamilton 2nd May
124GB / Fakenham 4th May
125GB / Wolverhampton 4th May
126GB / Nottingham 7th May
127GB / Nottingham 8th May
128GB / Hexham 8th May
129GB / Hamilton 14th May
130GB / Hexham 18th May
131GB / Wolverhampton 18th May
132GB / Nottingham 18th May
133GB / Wolverhampton 20th May
134GB / Fakenham 23rd May
135GB / Nottingham 23rd May
136GB / Wolverhampton 24th May
137GB / Wolverhampton 26th May
138GB / Hamilton 26th May
139GB / Nottingham 2nd Jun
140GB / Hamilton 3rd Jun
141GB / Hexham 5th Jun
142GB / Wolverhampton 8th Jun
143GB / Hamilton 9th Jun
Sheet1
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
How about
Excel Formula:
=COUNTIF(UKTable[TOWN],"* Ham*")
note the space after the 1st *. Although if you have any other towns that start with Ham, they will also be counted.
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top