Multiple Value Lookup Function - Looking for Solutions

[meikenbe]

Hi Everyone,

I'm not sure if similar solutions have been posted before---I've tried looking for them and have had no luck.

As you can see from the below table there are repeated "lookup values" which is the first challenge, but at the same time I want these "Engagement Codes" to be run through IF/AND/OR functions to bring back a Y/N for each service line.

Let's say that "Service Line" covers BAS and SFTSS. If the lookup finds that value, I want it to mark a Y. So, ideally the lookup would search "123150," (for all three instances) look through BAS, TXGEN and IES and check those against a nested IF statement to return a "Y" for Service Line, if the statement is true, blank, if not. This would be then repeated for "Service Line 2," but for different engagement codes.

Table from which I want info pulled:
Client Code Engagement Code
123150 BAS
123150 TXGEN
123150 IES
198923 IES
198923 SFTSS
198923 BENAU
198923 BENAU
198923 BAS
342090 SALT
342090 FWPLN

Table where I want it placed:
Client Code Service Line Service Line 2 Service Line 3
123150
198923
342090

Thanks for your help in advance.

 

[NARAYANK991]

Hi ,

If I have understood you correctly , each of the following :

Service Line , Service Line 2 , Service Line 3

is a combination of Engagement Codes.

You have given an example of Service Line being a combination of BAS and SFTSS.

Given the data in the first table , you want that the second table should be populated with Y where there are entries in the first table against the respective combinations of Engagement Codes.

For example , against 198923 , there would be a Y under Service Line.

To go further in this , you need to specify the combination of Engagement Codes which make up Service Line , Service Line 2 and Service Line 3. Otherwise , I don't think anything can be done.

Narayan

 

[Sam Mathai Chacko]

So what would be your output in this specific example? Just to understand your requirement...

 

[Sam Mathai Chacko]

Oops... sorry Narayan, I had the window open for a while and didn't realize you had posted.

 

[Faseeh]

Hi meikenbe,

Please see this file if it helps.

@Sam, I don't think Narayan will mind if you give a solution

 

[meikenbe]

Service Line = BAS and SFTSS
Service Line 2 = IES, TXGEN, SALT and FWPLAN
Service Line 3 = BENAU

For 198923, we have IES, SFTSS, BENAU, BENAU and BAS.

Yes, if we're considering 198923, the lookup should check each of it's 5 instances:

Service Line would return a "Y" (indicating a positive match) because of the presence of SFTSS and/or BAS

Service Line 2 would return a "Y" because of the presence of IES

Service Line 3 would return a "Y" because of the presence of BENAU

The lookup formula only needs to find one instance for it to return a positive (i.e. Service Line does not have to find both BAS and SFTSS, just one of them.)

Service Line, Service Line 2, and Service Line 3 are mutually exclusive. I believe my problem could be solved by just figuring out one of these columns.

 

[meikenbe]

Faseeh,


I see what you've done. However, I'm looking for these engagement codes to be grouped under an umbrella "Service Line". So BAS and SFTSS should not be considered separate, but 2 parts of a whole "Service Line."

Hi meikenbe,



Please see this file if it helps.



@Sam, I don't think Narayan will mind if you give a solution

 

[Faseeh]

meikenbe,

are these service lines present in one cell separated by coma or for each service line there are multiple cells?

 

[meikenbe]

Service Line, Service Line 2, and Service Line 3 should each be considered a head of a column.

Please see the attached file.

 

[Faseeh]

its already 1 am so i will try it tomorrow.

 

[Haseeb A]

Hello,

One way is; set up a lookup table, with all possible Codes then use COUNTIFS. So you can easily adjust lookup table.

See the attached.

 

[meikenbe]

Haseeb,

Great work around --- I just ran it and it seemed to have worked, although it froze processing for a bit because of the massive amount of entries.

Thank you all for your help.