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

trishcollins

New Member
Joined
Jan 7, 2006
Messages
34
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.

trishcollins

New Member
Joined
Jan 7, 2006
Messages
34
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)),
"No",
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

Threads
1,114,539
Messages
5,548,631
Members
410,861
Latest member
Victor96
Top