vlookup on table column with cells containing multiple entries separated by commas


New Member
Jan 7, 2006
I have a table that has a list of requirements from various documents. The table is called "Combined_Req". Each requirement is assigned a unique identifier "ID", that is in the form of "CR-1", "CR-2", etc."

I have another table called "Req_by_Category" where I have consolidated like requirements from the first table and given each one a unique identifier "ENC Req ID", that is in the form of "ENC-1", "ENC-2", "ENC-3", etc. There is a column in this table called "Original Requirement", where I have manually identified all the "CR-x" requirements from the "Combined_Req" table, so I can understand which requirements I have covered off with that new, single requirement. It takes on the form of concatenated CR requirements, so it may have one, two, or multiple identifiers in a single cell. For instance "CR-12, CR-14, CR110", or "CR-3", etc. Note that in a single instance, there is no comma, and in a multiple instance, the last value has no trailing comma.

ENC Req ID, Original Requirement
"ENC-1", "CR-24, CR- 82, CR- 83, CR- 85, CR- 86, CR- 191, CR- 197"
"ENC-4", "CR-13"

In the first table, "Combined_Req", I have a new column called "In ENC Requirements", and I want to do a lookup on the "Req_by_Category" table on the "Original Requirement" column to make sure that I have covered off all the requirements in the new "Req_by_Category" table.

I have tried this:

= VLOOKUP("*"&[@ID]&"*",Req_by_Category[Original Requirement],1,FALSE), and it only returns the result if the ID number being looked up in the in the first position. For example, if I look up "CR-101", then it returns the contents of the concatenated cell "CR-101, CR- 102", which is correct. However, in the very next row, the same formula on "CR-102" results in #N/A, when in fact, it should show the same result as the previous row "CR-101, CR-102". I have checked similar items, and have the same result. In addition, it also picks up any instance where the first item STARTS with the same number, ie. a lookup on "CR-2" brings back "CR-24", NOT "CR-2", as it's finding the first instance that matches the wild cards. So, there has to be an issue with my formula, and I know the wild cards are part of it. I am familiar with VBA, so if someone has code, that would be great.

It's not a huge deal to do manually, but if I decide that a different consolidated requirement is more suitable to the original, or as I add more requirements to the original requirement column (I am not quite done yet), I will have to manually make those changes. This way, if I change the contents of any of any cells in the Original Requirements column, it's automatically reflected in the combined requirements table.

I am just trying to initially show if a requirement has been covered off, and once I get the formula working, I want to instead, show the "ENC Req ID" in the "In ENC Requirements" column, so I can quickly look to see if and what requirement the original requirement is covered under. Ideally, a child-parent relationship would be great, but I don't expect miracles. I don't want to spend more time on this, then actually doing the work ;)

Any suggestions?

Trish ;)
Last edited:

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".


New Member
Jan 7, 2006
Nevermind, I figured it out using this nested IF statement:

=IF([@[Cloud Connectivity Design Consideration]]="Yes",
IF(ISNA(VLOOKUP([@ID],Req_by_Category[Original Requirement],1,FALSE)),
IF(ISNA(VLOOKUP([@ID]&","&"*",Req_by_Category[Original Requirement],1,FALSE)),
IF(ISNA(VLOOKUP("*"&", "&[@ID]&","&"*",Req_by_Category[Original Requirement],1,FALSE)),
IF(ISNA(VLOOKUP("*"&", "&[@ID],Req_by_Category[Original Requirement],1,FALSE)),
VLOOKUP("*"&", "&[@ID],Req_by_Category[Original Requirement],1,FALSE)),
VLOOKUP("*"&", "&[@ID]&","&"*",Req_by_Category[Original Requirement],1,FALSE)),
VLOOKUP([@ID]&","&"*",Req_by_Category[Original Requirement],1,FALSE)),
VLOOKUP([@ID],Req_by_Category[Original Requirement],1,FALSE)),"N/A")

I also tested to see if the requirement should be considered, which is the first IF statement.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics