VLOOKUP + MATCH

[BennyC]

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!

 

[BennyC]

To be fair sorting does have it's benefits and provide a clearer result (and it also works too btw) but I'm still keen to know if this can be done another way

 

[wesimmo]

I'm not 100% on what you're asking...

But using IF COUNTIF > 0, checking the count of the row's 10 columns in worksheet 1 against the variable allergen in worksheet 2's column header, then using the product name to find the relevant row, using MATCH to derive the row, would work I think?

 

[BennyC]

I'm not 100% on what you're asking...

But using IF COUNTIF > 0, checking the count of the row's 10 columns in worksheet 1 against the variable allergen in worksheet 2's column header, then using the product name to find the relevant row, using MATCH to derive the row, would work I think?

I think I follow, makes sense. Thanks.

 

[wesimmo]

I'm quite a visual person, if you cant work it out, post some dummy data.

You might need to know INDIRECT too.

 

[gpuerrilla]

What version of Excel you using?

365 and Office 2021 have xlookup function.

 

[BennyC]

So here is my source data:



And here is my LOOKUP+MATCH function:

(Albumen is in column BE which can't be seen.



So at the moment my limitation is needing to sort the rows in the Product Specification Upload sheet by column E to mirror the source data for the MATCH function to then show the correct corresponding result. Which isn't particularly problematic but is there another way to do this that is more robust?

 

[gpuerrilla]

1) Convert the data sources to a table.

either:
a) Use power query to pull it in, tidy up, assign key value pairs - then merge etc.
or
b) Add to the data model (assuming you can use your codes/sku's) and relate them together

You should be able to drop your new files into the source data periodically, refresh and all the work once setup has been done in seconds.

 

[Quartz]

Is there a cleverer approach? and is this do-able?

Are you using the right software? ISTM that Access might be a better solution. Put the data in 3rd Normal Form, put it in tables, and then match it. But I haven't done database work in over a decade.

And here is my LOOKUP+MATCH function:

Putting the name directly into the formula is a bad idea in Excel from a data integrity POV. Put the name in a cell, give the cell a name, and then refer to it via the name. It means you have to change only one cell when you change the spreadsheet; it's very easy to miss one and hilarity will likely ensue (e.g. in your second spreadsheet it's likely ST_PROD_1_BENTONITE, not ST_PROD_1_BETONITE).