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