vlookup/search/find values within a text cell

[TJ1982]

Hi, Newbie tothe forum and basic Excel user.

From a text cell (Column A) I am struggling to extract the Cost Centre(Column B) and report the Project Name (Column C).

In the table below, I need to see if the values in column B "CostCentre" exist in any part of the cells in column A "Cost Code",and if they do then report the text in column C "Project".

i.e. does "100000" exist in any of the cells in column A, if so report"Project 1" in the relevant cells.

I don't think a simple vlookup or find/search formula can do this?

Is there a way it can be done backwards? i.e. vlookup if any of the valueswithin each cell in column A exist in column B, if they do report the correspondingvalue found in column C.

To be clear columns B and C are related to each other i.e. Cost Centre 100000is Project 1.

Thanks in advance.


Tom.


[TABLE="width: 232"]
<tbody>[TR]
[TD="width: 159, bgcolor: transparent"] Cost Code
[/TD]
[TD="width: 80, bgcolor: transparent"] Cost Centre
[/TD]
[TD="width: 71, bgcolor: transparent"] Project
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 16586/100000/54545
[/TD]
[TD="bgcolor: transparent"]

100000​

[/TD]
[TD="bgcolor: transparent"] Project 1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 100006
[/TD]
[TD="bgcolor: transparent"]

100001​

[/TD]
[TD="bgcolor: transparent"] Project 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 16586 100002/54547
[/TD]
[TD="bgcolor: transparent"]

100002​

[/TD]
[TD="bgcolor: transparent"] Project 3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 16586/100003/545HB
[/TD]
[TD="bgcolor: transparent"]

100003​

[/TD]
[TD="bgcolor: transparent"] Project 4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 16586/100001/54549
[/TD]
[TD="bgcolor: transparent"]

100004​

[/TD]
[TD="bgcolor: transparent"] Project 5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 16586 :100005/54550
[/TD]
[TD="bgcolor: transparent"]

100005​

[/TD]
[TD="bgcolor: transparent"] Project 6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 16586/100004/54551
[/TD]
[TD="bgcolor: transparent"]

100006​

[/TD]
[TD="bgcolor: transparent"] Project 7
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 100008/54552
[/TD]
[TD="bgcolor: transparent"]

100007​

[/TD]
[TD="bgcolor: transparent"] Project 8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 16586/100007/545.53
[/TD]
[TD="bgcolor: transparent"]

100008​

[/TD]
[TD="bgcolor: transparent"] Project 9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 16586 /100009/54554
[/TD]
[TD="bgcolor: transparent"]

100009​

[/TD]
[TD="bgcolor: transparent"] Project 10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 16586/100010/545.55
[/TD]
[TD="bgcolor: transparent"]

100010​

[/TD]
[TD="bgcolor: transparent"] Project 11
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

I can't think of a simple way to do this, particularly as the inputs seem to follow an irregular pattern.

If you could write some rules to identify the cost centre within the inputs, then you could write a formula to isolate the cost centre from the inputs, and then lookup the cost centre against your reference table.

A possible rule to do this could be as follows
1) If the input contains the text string "/5", then take the preceding 6 digits as the cost centre.
2) If the input does NOT contain the text string "/5", then use the entire input as the cost centre.

This seems to be right for the sample data you provided, and if so it can be written as a formula.

QUESTION - is this ruleset correct for ALL your data ?
If yes, great, we can use that.
If no, is the amount of further variation small, so that we can adapt the ruleset to deal with one or two more variations ?
Or is the further variation huge, to the extent that it will not be possible to create a reliable ruleset in this way ?

 

[TJ1982]

Thanks for the reply.

Unfortunately the variation is huge and un-uniform in its format. Isolating the cost centre is my challenge, which is why I was hoping there was a way to a type of reverse vlookup or find formula.

 

[Fluff]

One option, with a helper column

Book1
	A	B	C	D	E
1	Cost Code	Cost Centre	Project
2	16586/100000/54545	100000	Project 1	16586/100000/54545	Project 1
3	100006	100001	Project 2	16586/100001/54549	Project 7
4	16586 100002/54547	100002	Project 3	16586 100002/54547	Project 3
5	16586/100003/545HB	100003	Project 4	16586/100003/545HB	Project 4
6	16586/100001/54549	100004	Project 5	16586/100004/54551	Project 2
7	16586 :100005/54550	100005	Project 6	16586 :100005/54550	Project 6
8	16586/100004/54551	100006	Project 7	100006	Project 5
9	100008/54552	100007	Project 8	16586/100007/545.53	Project 9
10	16586/100007/545.53	100008	Project 9	100008/54552	Project 8
11	16586 /100009/54554	100009	Project 10	16586 /100009/54554	Project 10
12	16586/100010/545.55	100010	Project 11	16586/100010/545.55	Project 11
Program start

Cell Formulas
Range	Formula
E2	=INDEX($C$2:$C$12,MATCH(A2,$D$2:$D$12,0))
D2	{=INDEX($A$2:$A$12,MATCH("*"&B2&"*",$A$2:$A$12&"",0))}
Press CTRL+SHIFT+ENTER to enter array formulas.