Need Help Retrieving Values from Columns

[Excell1677]

Hello,

I'm stuck in a bit of an problem and hoping someone could shine a light on this...The problem is I'm trying to figure out a method to find the value that matches in a column and if it matches the table then return that value in column B. The problem is that within a given row there could be multiple values that may match the values in the reference table so I would like to find a method that would return multiple values in a list format. I provided an example in a spreadsheet file and the goal I'm hoping to achieve as well, Any help would be greatly appreciated!

Thanks for any help!

[Richard Buttrey]

Hi,

You'd be better advised to record your data in a normalised 2 dimensional table. See Sheet 2 in the attached.
This allows you to use a Pivot Table to analyse your data and avids the need for formulae or VBA altogether. I've also added a SLicer to allow you to filter the PT for particular subjects.

[alansidman]

Here is a VBA solution

Please Login or Register  to view this content.

[Excell1677]

Thanks for your help on this problem,

1) Thank you Richard for taking a look at this the only problem with the solution you provided is that that example is actually part of a bigger table which I cannot change because it's part of a bigger spreadsheet..

2) Thank you alansidman for taking a look as well this solution is more in line for what I'm looking for...A few questions concerning this code..First is there a way to remove the ";" in front of the first keyword for example, (;class1; class2). In addition if the ref table is on a different workbook how would I reference it in the vba code for example if the ref table was in a worksheet called "Values for Table.xls" and the table was located from A2:B9? Thanks again!

[alansidman]

As to the ";"you have a choice. Either the way it is or just a space and no punctuation. I have changed it so that there is only a space and no punctuation.

As to the look up table, the below code should work so long as both workbooks are open

Please Login or Register  to view this content.

[Excell1677]

Thanks alansidman! This works like a charm! Thanks for your help with this!

[alansidman]

You are welcome. Thanks for the Rep