# Index find next no match

#### Bonkers Bill

##### Active Member
Hi every one, i am trying to index a list 'Got Upload'!A\$1:A\$1325, find the 1st one that is not in another list Remix!K\$2:K\$795, drag this down so it finds the 2nd, 3rd and so on
I know this formula is total rubbish but i dont know where to start, these are the ranges, any help would be great.
Today i will mostly be sailing down our street

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

##### MrExcel MVP
Try to post a tiny sample along with the expected results...

#### Bonkers Bill

##### Active Member
Heres another way of saying the formula that might help,
if(index(Remix!K\$2:K\$795,match('Got Upload'!A\$1,Remix!K\$2:K\$795) is true, then match'Got Upload'!A\$2 and so on, if false, enter result 'Got Upload'!A\$1 or which ever is false, hope this makes more sense.

#### Bonkers Bill

##### Active Member
Got
D001
D002
D003
D004
D005
D006

Remix
D001
D002
D004
D006
D007
D008
I am looking for the numbers in Got that is not in remix, i am looking for a formula that i can enter in box A1 and drag it down the column, A1 result is D003, A2 result is D005 and so on, the numbers in remix are not in order and i'm not 100% sure that Got are

#### facethegod

##### Well-known Member
If your GOT range is in A1:A6 and your REMIX range is in B1:B6 Then try this in C1 and drag down:

Code:
``=INDEX(A:A,SMALL(IF(ISNA(MATCH(\$A\$1:\$A\$6,\$B\$1:\$B\$6,0)),ROW(\$A\$1:\$A\$6),""),ROWS(\$1:1)),1)``

HTH

#### Bonkers Bill

##### Active Member
This is the formula i used
it works on the 1st number then returns#NUM for the rest

#### Bonkers Bill

##### Active Member
This is the formula i used
it works on the 1st number then returns#NUM for the rest

#### Bonkers Bill

##### Active Member
This is the formula i used
it works on the 1st number then returns#NUM for the rest

#### facethegod

##### Well-known Member
Did you confirm it w/ Ctr + Shift + Enter, not just Enter?

#### Bonkers Bill

##### Active Member
Thanks facethegod that works great, there is another bit to this if you could help, the formula returns more than one result that is the same eg if there are 3 D0001 it returns 3, i would like the formula to only return 1 of each, no duplicates, do you know how to do this. Cheers
Today I will set sail to find land after the floods!

Replies
5
Views
937
Replies
8
Views
312
Replies
3
Views
256
Replies
3
Views
903
Replies
15
Views
1K

1,181,056
Messages
5,927,864
Members
436,573
Latest member
CMR237

### 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.

### Which adblocker are you using?

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

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