Formula to compare two unequal lists including duplicates

[mistersee]

Hi,

I have two lists (unequal) and would like to be able to return a simple yes or no next to the commodity description in column F if the commodity is listed in column A.

I should note that the array in columns E:F is 124616 rows. I have therefore tried to use a nested Match function within Index (as I have read that this will make calculating quicker for larger datasets) but I think it might be struggling as there are duplicates (required unfortunately) in the second array.

I would then be looking to filter the list in array E:F to leave only the commodities in the required list (column A).

I hope this makes sense. I've attached a picture (hopefully) showing the layout of the lists, I appreciate that they will more than likely need to be chopped and changed to fit within a function's requirements.

Not sure if the picture has attached so will link here
https://drive.google.com/open?id=1TFJ5LNPbmoPtEn2HSkUdFt_pu1O4nu-e

Any help would be much appreciated.

Thanks!

 

[steve the fish]

Hi. It looks like the list of commodity codes in column B is sorted by the code so you could use:

=IF(VLOOKUP(E2,B:B,1)=E2,"yes","no")

It will be faster than an exact match vlookup but does require the list of codes to be sorted.

 

[etaf]

If column A and Column F description are identical
IE
Pure-bred breeding horses in F2 to F9
if F2 matches those words exactly in A2:A124616 then in Column G you want a YES
no match would be a NO

if thats the case then a COUNTIF() should work
in G2 put

=Countif( $A$2:$A$124616, F2)

IF( Countif( $A$2:$A$124616, F2) > 0 , "Yes", "No")
and copy down