match, lookup

[FROGGER24]

I need to compare range AW1:AW which contains a list of numbers This list will typically have less than 20 rows . I need to look in column B and find all of the matches and insert a "X" in column AV if there are matches

 

[jasonb75]

Try

=IF(ISERROR(VLOOKUP(AW1,B$1:B$20,1,FALSE)),"","X")

With B$20 changed to the last cell in column B to look at for a match, then fill down in AV as needed.

Hope this helps

 

[FROGGER24]

Column AW is the "masterlist" of 1-20 numbers. In column AV2 I need to put a "X in that row if column B matches any of the numbers listed in column AW. There can be several instances of the number in column B and all of them will need to have a X in column AV. the worksheet will be different lengths each month.

 

[jasonb75]

Sorry, had your columns round the wrong way, thought you wanted AW compared to B

Haven't tested this one, but think this is what you need.

=IF(B2<>"",IF(ISERROR(VLOOKUP(B2,AW$1:AW$100,1,FALSE)),"","X"),"")

Jason

edit: added an extra check to ignore blanks so AW range can be made long enough to cover any extra entries without causing problems.

 

[FROGGER24]

That worked great, Thank you Very much for your help!