Delimit and lookup value in a cell

[Justplainj]

Hi All,

I have a table (see example below) where column B has a generic title (tied to salary benchmarks) and then column C has the job titles used within the business linked to the generic titles. The 'other info' columns will have information such as salary benchmark for the various percentiles, etc.
This forms part of a workforce planning tool that I am building.

On a different sheet I have a drop down list where the person chooses a single the Job Title (Column C).

What I am trying to achieve is when the drop down choice is in example, Junior Accountant, it must lookup Junior Accountant in column C which will then find that row and I can then display/pull through the necessary information, i.e. Title, or other info columns as needed.
There are some titles (column B) that may have the same job title, i.e. the job title may be Accountant but can fall under Accountant I or Accountant II.
I then use a dependent drop down list with the Indirect() formula referencing the correct named range. This means the lookup formula might have to look at column B and C and then provide the other info.

The lookup formula and referencing two columns at once is the easy part, but it finding the delimited value in column C is what i am struggling with.
If possible i do not want to split the job titles into several columns as some of the job titles are more than 10 as we are quite a large organisation and that will increase the amount of columns that needs to be referenced considerably.

Reference	Title	Job Titles	Other Info	Other Info
0001	ACCOUNTANT I (WITH CA)	Credit Modeling Analyst, Accountant
0002	ACCOUNTANT I (WITHOUT CA)	Junior Accountant, Book Keeper
0003	ACCOUNTANT II (WITH CA)	Financial Modeling Analyst, Financial Planner
0004	ACCOUNTANT II (WITHOUT CA)	Accounting Administrator, Junior FP
0005	ACCOUNTANT III (WITH CA)	Senior Accountant, Financial Manager

Thank you in advance
J

 

[bebo021999]

To identify "Accountant" vs "Junior Accountant", try to make sure job title to be: ", Accountant," or ", Junior Accountant," (With trailing ", " and ending with ",")
With C11 is dropdown, try to search (", "&$C$11&",") within list (", "&$C$3:$C$7&",") to get relevant information in same row.
A13, to get reference

 =LOOKUP(2,1/ISNUMBER(SEARCH(", "&$C$11&",",", "&$C$3:$C$7&",")),A$3:A$7)

Drag accross

excel_Book1 (1).xlsx
	A	B	C	D	E
1
2	Reference	Title	Job Titles	Other Info	Other Info
3	1	ACCOUNTANT I (WITH CA)	Credit Modeling Analyst, Accountant	a	b
4	2	ACCOUNTANT I (WITHOUT CA)	Junior Accountant, Book Keeper	c	d
5	3	ACCOUNTANT II (WITH CA)	Financial Modeling Analyst, Financial Planner	e	f
6	4	ACCOUNTANT II (WITHOUT CA)	Accounting Administrator, Junior FP	g	h
7	5	ACCOUNTANT III (WITH CA)	Senior Accountant, Financial Manager	i	j
8
9
10
11		Select Job Title	Book Keeper
12
13	2	ACCOUNTANT I (WITHOUT CA)	Junior Accountant, Book Keeper	c	d
Sheet2

Cell Formulas
Range	Formula
A13:E13	A13	=LOOKUP(2,1/ISNUMBER(SEARCH(", "&$C$11&",",", "&$C$3:$C$7&",")),A$3:A$7)

 

[Justplainj]

Good day Bebo021999

This works very well thank you for the solution (will mark as such).

I understand the may I ask if it is possible to explain the format of the formula?

I used F9 to break down the formula and this is my results.

=LOOKUP(2,1/ISNUMBER( {#VALUE!;20;#VALUE!;#VALUE!;#VALUE!} ),A$3:A$7

=LOOKUP(2,1/ {FALSE;TRUE;FALSE;FALSE;FALSE} ,A$3:A$7

What I am struggling to understand as part of this formula is the 1 divided by the array {FALSE;TRUE;FALSE;FALSE;FALSE} as used for the lookup vector.

Thank you,

 

[Peter_SSs]

What about this formula in A13 (and no need to copy across)?

22 03 16.xlsm
	A	B	C	D	E
1
2	Reference	Title	Job Titles	Other Info	Other Info
3	1	ACCOUNTANT I (WITH CA)	Credit Modeling Analyst, Accountant	a	b
4	2	ACCOUNTANT I (WITHOUT CA)	Junior Accountant, Book Keeper	c	d
5	3	ACCOUNTANT II (WITH CA)	Financial Modeling Analyst, Financial Planner	e	f
6	4	ACCOUNTANT II (WITHOUT CA)	Accounting Administrator, Junior FP	g	h
7	5	ACCOUNTANT III (WITH CA)	Senior Accountant, Financial Manager	i	j
8
9
10
11		Select Job Title	Book Keeper
12
13	2	ACCOUNTANT I (WITHOUT CA)	Junior Accountant, Book Keeper	c	d
14
FILTER

Cell Formulas
Range	Formula
A13:E13	A13	=FILTER(A3:E7,ISNUMBER(SEARCH(", "&C11&",",", "&C3:C7&",")),"")
Dynamic array formulas.

 

[Justplainj]

Thank you Peter_SSs

 

[Peter_SSs]

You're welcome.

 

[bebo021999]

Good day Bebo021999

This works very well thank you for the solution (will mark as such).

I understand the may I ask if it is possible to explain the format of the formula?

I used F9 to break down the formula and this is my results.

=LOOKUP(2,1/ISNUMBER( {#VALUE!;20;#VALUE!;#VALUE!;#VALUE!} ),A$3:A$7

=LOOKUP(2,1/ {FALSE;TRUE;FALSE;FALSE;FALSE} ,A$3:A$7

What I am struggling to understand as part of this formula is the 1 divided by the array {FALSE;TRUE;FALSE;FALSE;FALSE} as used for the lookup vector.

Thank you,

Thats why LOOKUP is very strong function.
See how lookup works:
=LOOKUP(3,{1,2,3,4},{"A","B","C","D"}) = "C" => exact lookup (number 3)
=LOOKUP(3,{1,2,4,4},{"A","B","C","D"}) = "B" => lookup largest value that smaller (number 2)
=LOOKUP(n,{1,1,1,1},{"A","B","C","D"}) = "D" => with any n>1 (2 or 3 or4 ...), lookup the last value that smaller n (the last number 1)
also work with #error value in range:
=LOOKUP(2,{1,#error,1,#error},{"A","B","C","D"}) = "C" =>, lookup the last value that smaller 2 (the last number 1)
that why we try to create a range of 1 and #error
1/{FALSE;TRUE;FALSE;FALSE;FALSE} = 1/(0,1,0,0,0} = {#error,1,#error,#error,#error}
so, it becomes:

=LOOKUP(2,1/ {FALSE;TRUE;FALSE;FALSE;FALSE} ,A$3:A$7 => returns cell A4 value

Hope it helps.

 

[jtakw]

Hi,

Pardon my intrusion, but the ISNUMBER test is not needed, it could just be:

=LOOKUP(2,1/SEARCH(", "&$C$11&",",", "&$C$3:$C$7&","),A$3:A$7)