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.
<tbody>
</tbody>
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.
Cost Code | Cost Centre | Project |
16586/100000/54545 | 100000 | Project 1 |
100006 | 100001 | Project 2 |
16586 100002/54547 | 100002 | Project 3 |
16586/100003/545HB | 100003 | Project 4 |
16586/100001/54549 | 100004 | Project 5 |
16586 :100005/54550 | 100005 | Project 6 |
16586/100004/54551 | 100006 | Project 7 |
100008/54552 | 100007 | Project 8 |
16586/100007/545.53 | 100008 | Project 9 |
16586 /100009/54554 | 100009 | Project 10 |
16586/100010/545.55 | 100010 | Project 11 |