Index find next no match

Bonkers Bill

Active Member
Joined
Feb 14, 2005
Messages
347
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
=index('Got Upload'!A$1:A$1325(nomatch(Remix!K$2:K$795,'Got Upload'!A$1:A$1325),0)
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.

Bonkers Bill

Active Member
Joined
Feb 14, 2005
Messages
347
=index('Got Upload'!A$1:A$1325(nomatch(Remix!K$2:K$795,'Got Upload'!A$1:A$1325),0)
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
Joined
Feb 14, 2005
Messages
347
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
Joined
Aug 1, 2006
Messages
767
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
Joined
Feb 14, 2005
Messages
347
This is the formula i used
=INDEX('Got Upload'!A:A,SMALL(IF(ISNA(MATCH('Got Upload'!A$1:A$5000,Remix!K$2:K$5001,0)),ROW('Got Upload'!A$1:A$5000),""),ROWS($1:1)),1)
it works on the 1st number then returns#NUM for the rest
 

Bonkers Bill

Active Member
Joined
Feb 14, 2005
Messages
347
This is the formula i used
=INDEX('Got Upload'!A:A,SMALL(IF(ISNA(MATCH('Got Upload'!A$1:A$5000,Remix!K$2:K$5001,0)),ROW('Got Upload'!A$1:A$5000),""),ROWS($1:1)),1)
it works on the 1st number then returns#NUM for the rest
 

Bonkers Bill

Active Member
Joined
Feb 14, 2005
Messages
347
This is the formula i used
=INDEX('Got Upload'!A:A,SMALL(IF(ISNA(MATCH('Got Upload'!A$1:A$5000,Remix!K$2:K$5001,0)),ROW('Got Upload'!A$1:A$5000),""),ROWS($1:1)),1)
it works on the 1st number then returns#NUM for the rest
 

Bonkers Bill

Active Member
Joined
Feb 14, 2005
Messages
347
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!
 

Forum statistics

Threads
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.
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
Top