Evening,
I'll try to explain this as simply as possible.
Workbook 1: I have one spreadsheet with a list of new products, which shows 1 of 27 different variables (allergens as text) over a range of 10 adjacent columns. There is never more than 10 for any single product.
This information isn't always ordered consistently so which of the 10 columns some of these 27 allergens will show in is not consistent. Column 1 might be 'Milk' for one product but could say 'Egg' for another and show 'Milk' in column 2 instead.
Workbook 2: I have a much larger list of products which also includes the 'new' products from Workbook 1. I have 27 different columns (one for each allergen) with the name of each variable as the title.
I have successfully used VLOOKUP and MATCH to return a Y / N result in each of the 27 columns but am restricted to looking at only 1 row in Workbook 2 with the MATCH function. I can't use MATCH over a range of cells spanning more than 1 row.
The plan was to copy this formula down over all the rows in Workbook 2 and where the lookup finds the product in the list, it would then look in Workbook 1 to cross reference allergens. I will then go on to take this data and upload it elsewhere.
Is there a cleverer approach? and is this do-able?
I suppose I could use a lookup to 'number' the products in workbook 2 to match the product rows in Workbook 1. Then sort workbook two and then use the MATCH function this way instead but it feels a little more clunky and vulnerable and requires more steps than I would like for the end user.
Suggestions welcome (without the use of a macro preferably as this isn't an area I have any skill or expertise in).
Thanks!
I'll try to explain this as simply as possible.
Workbook 1: I have one spreadsheet with a list of new products, which shows 1 of 27 different variables (allergens as text) over a range of 10 adjacent columns. There is never more than 10 for any single product.
This information isn't always ordered consistently so which of the 10 columns some of these 27 allergens will show in is not consistent. Column 1 might be 'Milk' for one product but could say 'Egg' for another and show 'Milk' in column 2 instead.
Workbook 2: I have a much larger list of products which also includes the 'new' products from Workbook 1. I have 27 different columns (one for each allergen) with the name of each variable as the title.
I have successfully used VLOOKUP and MATCH to return a Y / N result in each of the 27 columns but am restricted to looking at only 1 row in Workbook 2 with the MATCH function. I can't use MATCH over a range of cells spanning more than 1 row.
The plan was to copy this formula down over all the rows in Workbook 2 and where the lookup finds the product in the list, it would then look in Workbook 1 to cross reference allergens. I will then go on to take this data and upload it elsewhere.
Is there a cleverer approach? and is this do-able?
I suppose I could use a lookup to 'number' the products in workbook 2 to match the product rows in Workbook 1. Then sort workbook two and then use the MATCH function this way instead but it feels a little more clunky and vulnerable and requires more steps than I would like for the end user.
Suggestions welcome (without the use of a macro preferably as this isn't an area I have any skill or expertise in).
Thanks!
