Match or identify if a product is in a list

  • Thread starter Thread starter widman
  • Start date Start date
W

widman

I have a list of 6000 products in column A of sheet 1of a workbook. In the
next sheet I have a column of those 50 products company X uses, 130 products
that company Y uses, etc.
If I add the column headings for each company to sheet 1, how can I bring to
that column something (like the number "1") if that product is used by the
company in that column?
 
Presume your "next sheet" is Sheet2, with company names in row1 across,
products listed in row2 down

In Sheet1,
Put in B2:
= --(COUNTIF(OFFSET(Sheet2!$A:$A,,MATCH(B$1,Sheet2!$1:$1,0)-1),$A2)>0)
Copy B2 across/fill down to last row of data in col A
 
In cell B1 of the 1st sheet put this and then drag it down for the full 6000
rows.
Substitute the relevant ranges for each company column in sheet2.

=IF(ISNA(VLOOKUP(A1,Sheet2!A1:A50,1,FALSE)),0,1)
 
Great, I had to replace your commas with seimcolons because I use a software
that requires separators in Windows to be semicolons, but it worked great.
thanks
 
Back
Top