How can I get match data from a comma separated list to pull cell value from same row?

[brhudgens]

Hello,

I have 2 tabs in my workbook. The first tab (Vlist) looks something like this:

	A	B	C	D+
1	CODE	FORMAT	Item#	many more columns-->
2	C01	X	1598, 1599, 1600, 1601, 1604, 1608, 1609, 1611, 1612, 1613, 1614, 1616, 1629, 1630, 1631, 1632, 1633, 1634, 1635, 1636, 1637, 1638, 1639, 1640, 1641, 1643, 1644, 1649, 1650, 1651, 1652, 1654, 1656, 1657, 1658, 1913, 1914, 1915, 1916, 1917, 1918, 1920, 1921, 1922, 1923, 1924, 1925, 1928, 1929, 1932, 1935, 1939, 1940, 1950, 4973, 4974, 6690
3	C02	Y	1930, 1936
4	C03	Z	1645
5	C04	W	1603
6	C05
up to 64	C64

<tbody>
</tbody>

The second tab (Recap) looks like this:

	A	B	C	D
1	Item#	ItemName	Code	more columns of data-->
2	1598	Name1599	C01
3	1599	Name1600	C01
4	1600	Name1601	C01
5	1603	Name1645	C03
6	1930	Name1930	C02
up to 200

<tbody>
</tbody>

The restrictions that have been placed on the solution are as follows:
- no duplicate data entry required from the user
- no macros
- no additional interaction required from the user entering the data to make it work (no sorting or other manipulation).
- The VList tab is in order by CODE, The Recap tab is in order by Number

My goal is to do the following:
- CODE is already populated in the VList tab and has to be updated infrequently.
- Item# is already populated in the Recap tab and has to be updated infrequently.
- The relationship between the Code and the Item# changes weekly, so I need to do one of the following to avoid having to have the user do duplicate data entry:

A) Have the User enter the Item#s into the VList tab in a comma separated list to show all the Item#s that apply to a Code. Then in the Recap tab be able to use the Item# to find the corresponding code and populate the Code column in the Recap tab.

OR

B) Have the User enter the Codes in the Recap tab for each Item#, then use the Code from the VList tab to get a comma separated list of all of the Item#s that have that code.

​

I don't have a good solution for either option at this point, given the restrictions.

I've always had a lot of luck finding great solutions on this forum and I hope that you can help me with this puzzle. Please let me know if there's any additional info I can provide to clear up my request.

Thanks very much!

 

[mikerickson]

MATCH takes wild cards.

In Recap!C2, you could put the formula =INDEX(Vlist!A:A, MATCH("*"A2&"*", Vlist!C:C, 0), 1)

 

[brhudgens]

Sorry for my delay in replying - pulled off to another project, but I'm back on this now....

Thanks for your response. This works great!

Do you know of a way that I could generate the comma separated listing in Vlist (column C) if the user fills in the Code in Recap column C? The comma separated list is harder for the users to manage manually and they would like to go the other direction, entering the Code on Recap and the comma separated list on Vlist being generated for them. Thoughts?