Need to pull different values based on text from one sheet to another

Morning/Afternoon/Evening All,

For starters it has been over a decade since I did anything constructive on excel and I'm not afraid to say that excel has come a long way since then and I have basically forgot what I knew.

I have a list of over 1000 items in my warehouse (The list would also be ever expanding on a weekly basis with new models). Each of these items has a designated weight. A sample list of the items and weights are in TEST WEIGHTS

I am currently separating each worker (17 of them) and adding in the weight of the item separately. The relevant items for the driver then goes to a word document and gets signed by the drivers so they can go out on the road.

The report I run has multiple useless columns which I remove for the final excel table. I am happy to keep these in if it makes it easier and just minimize them. The report that runs is TEST. I am after the weights of the items to populate column AH automatically based on the model that is in column Y. (If it needs to be AI that is populated that is fine) I have removed all confidential data and replace with "a" but that's the only change made.

I realize this is a big ask as everything I have found to help can recognize the data in Column Y and add a set value but I need it to add to representative weight of that item.

The TEST WEIGHTS is only a selection of what is here and if it made it easier I could paste that into a 2nd tab on the report if that made it easier. I hope I have explained this correctly, any questions please ask.

This has worked but only up to a point. This is ran daily and is different items in different orders on a daily basis. The formula has stopped working as soon as it hit something that wasn't in the same order as the TEST WEIGHT list. Would me saving it as a different extension mean better results or enable different options?

Alansidman - This one has worked and seems to be perfect. If I were to extend the list in TEST WEIGHTS all I would have to do is alter the "197" in the formula to represent the last used row, yes?

Honestly guys, thank you for this. Solved within a day and you have saved literal weeks worth of man hours per year. Thank you.

Originally Posted by Mistah Jay

The formula has stopped working as soon as it hit something that wasn't in the same order as the TEST WEIGHT list.

It should look for an exact match regardless of order. If you're having a problem it's not related to the order. Double check that the formula matches the above.

Originally Posted by Mistah Jay

Alansidman - This one has worked and seems to be perfect. If I were to extend the list in TEST WEIGHTS all I would have to do is alter the "197" in the formula to represent the last used row, yes?

Yes.

The only difference between the two formulas is the number of rows. Otherwise you should get equivalent results.