Xlook-up duplicates

Clifty24

New Member
Joined
Aug 26, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Morning/Afternoon,

Can someone please help me in resolving this issue , i currently have a data capturing device set up that fills excel with the data, due to the size of the data area being captured , i use a separate sheet to quantify and categorise everything, then i pull the top 10 values from each sheet, i currently have the issue of if as shown on the picture i get duplicate values of say '10' the x-lookup will only return the first adjacent data , and never looks for the next data set that belongs to ten.

(Left image is correct return i'm looking for but i only get that through sort & Filter which due to the array of data being collected isn't good for me as i need to ideally automate the form with formulas)

(Centre image displays the formula used)

(Right image is the current return from my formula)
Correct .jpg
Formulae.jpg
Duplicate .jpg


Can anyone please help as i would be really grateful , If someone knows how to return blanks on the zeros within the same formula that's pulling large that would be ideal also, thanks for taking the time to read Guys & Girls.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCDEFGHI
1GridRefCountyCount
2SU963450Surrey720TQ318889Greater London
3TQ301779Greater London1820SE238348West Yorkshire
4ST775646Somerset320SK598040Leicestershire
5SD486463Lancashire1119NZ264704Tyne and Wear
6SU804602Hampshire1519SU152855Wiltshire
7TQ182849Greater London919TA281084Lincolnshire
8TQ318889Greater London2018TQ301779Greater London
9SE238348West Yorkshire2018TQ349861Greater London
10SU357218Hampshire1216SK522069Leicestershire
11TQ349861Greater London1816SK386915South Yorkshire
12TL329130Hertfordshire14
13SJ937240Staffordshire9
14NZ264704Tyne and Wear19
15SK542440Nottinghamshire5
16SU152855Wiltshire19
17SK012397Staffordshire11
18SK018938Derbyshire2
19SK522069Leicestershire16
20NZ427194County Durham8
21TQ138759Greater London11
22TQ184113West Sussex12
23TQ407827Greater London11
24TA281084Lincolnshire19
25SU876803Berkshire10
26SK598040Leicestershire20
27SK386915South Yorkshire16
28TQ105606Surrey16
29SP547958Leicestershire9
30SP088839West Midlands5
31
Sheet3
Cell Formulas
RangeFormula
G2:I11G2=INDEX(SORT(A2:C3000,3,-1),SEQUENCE(MIN(COUNT(C2:C30000),10)),{3,1,2})
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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