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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.


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