asked on # Tricky lookup and concatenate formula

Hi Experts using excel 2010

See attached workbook

Need a formula that does the following look the I'd in column A and if we have a Y in column b then find the corresponding Y in range c2:p5 and also if we have a Y in column q and a Y in second data range r2:v5 then return back results as shown in column x.

Book2.xlsx

See attached workbook

Need a formula that does the following look the I'd in column A and if we have a Y in column b then find the corresponding Y in range c2:p5 and also if we have a Y in column q and a Y in second data range r2:v5 then return back results as shown in column x.

Book2.xlsx

Microsoft Excel

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Made even longer in the process of dealing with the surplus comma after the last entry from each key range and the requirement for "/" or not:

=$A2&"("&IF($B2="Y",SUBSTITUTE(IF(C2="","",C$1&",")&IF(D2="","",D$1&",")&IF(E2="","",E$1&",")&IF(F2="","",F$1&",")&IF(G2="","",G$1&",")&IF(H2="","",H$1&",")&IF(I2="","",I$1&",")&IF(J2="","",J$1&",")&IF(K2="","",K$1&",")&IF(L2="","",L$1&",")&IF(M2="","",M$1&",")&IF(N2="","",N$1&",")&IF(O2="","",O$1&",")&IF(P2="","",P$1&",")&IF($Q2="Y","/",""),",","",COUNTA(C2:P2)),"")&IF($Q2="Y",SUBSTITUTE(IF(R2="","",R$1&",")&IF(S2="","",S$1&",")&IF(T2="","",T$1&",")&IF(U2="","",U$1&",")&IF(V2="","",V$1&","),",","",COUNTA(R2:V2)),"")&")"

=$A2&"("&IF($B2="Y",SUBSTI

For the key2 values will there be multiple entries or will it always be only one of the 5 issues?

If so, the second half (in bold) can be shorter:

=$A2&"("&IF($B2="Y",SUBSTITUTE(IF(C2="","",C$1&",")&IF(D2="","",D$1&",")&IF(E2="","",E$1&",")&IF(F2="","",F$1&",")&IF(G2="","",G$1&",")&IF(H2="","",H$1&",")&IF(I2="","",I$1&",")&IF(J2="","",J$1&",")&IF(K2="","",K$1&",")&IF(L2="","",L$1&",")&IF(M2="","",M$1&",")&IF(N2="","",N$1&",")&IF(O2="","",O$1&",")&IF(P2="","",P$1&",")&IF($Q2="Y","/",""),",","",COUNTA(C2:P2)),"")&**IF($Q2="Y",INDEX($R$1:$V$1**,MATCH("Y",$R2:$V2,0)),"") &")"

If so, the second half (in bold) can be shorter:

=$A2&"("&IF($B2="Y",SUBSTI

Your help has saved me hundreds of hours of internet surfing.

fblack61

I have been looking at this again to see if I can come up with anything shorter.

Is there a maximum or fixed number of Y entries in Key1 range? With your sample, both entries where there are entries in the Key1 range, there are 3 entries. If we knew that there would only be 3 entries then it could be reduced to checking three ranges based on the placing of the Y.

Alternatively, it could be done with a User Defined Function (UDF). A UDF is basically a bit of VBA that is used in the same way as a formula. This would mean changing the workbook to an xlsm though with the subsequent allowing of macros which that would enforce.

Thanks

Rob H

Is there a maximum or fixed number of Y entries in Key1 range? With your sample, both entries where there are entries in the Key1 range, there are 3 entries. If we knew that there would only be 3 entries then it could be reduced to checking three ranges based on the placing of the Y.

Alternatively, it could be done with a User Defined Function (UDF). A UDF is basically a bit of VBA that is used in the same way as a formula. This would mean changing the workbook to an xlsm though with the subsequent allowing of macros which that would enforce.

Thanks

Rob H

As I thought, it can be done with a UDF; see attached. The script of the Function is below:

Name As Range - The name to be included at start of final result, column A (ID)

Ch1 As Range - The cell to be checked for Key1 values, column B (Key1)

Key1 As Range - The range of cells containing Y as a result of Key1, columns C to P

Res1 As Range - The range of cells containing the result of Key1, columns C to P (1 to 14)

Ch2 As Range - The cell to be checked for Key2 values, column Q (Key2)

Key2 As Range- The range of cells containing Y as a result of Key2, columns R to V

Res2 As Range - The range of cells containing the result of Key2, columns R to V (Issue 1 to 5)

It is used in the spreadsheet like a formula with the following syntax:

=CheckKey($A2,$B2,$C2:$P2,$C$1:$P$1,$Q2,$R2:$V2,$R$1:$V$1)

$A2 = Name

$B2 = Ch1

$C2:$P2 = Key1

$C$1:$P$1 = Res1

$Q2 = Ch2

$R2:$V2 = Key2

$R$1:$V$1 = Res2

Note the $ locking the row for Res1 and Res2; this will ensure the results are still taken from row 1 when the formula is copied down to more rows.

Any further questions, please let us know.

Thanks

Rob H

Lookup-UDF.xlsm

```
Function CheckKey(Name As Range, Ch1 As Range, Key1 As Range, Res1 As Range, Ch2 As Range, Key2 As Range, Res2 As Range)
If Ch1 = "Y" Then
Str1 = ""
Count1 = 0
For Each Cell In Key1
Count1 = Count1 + 1
Next Cell
For C = 1 To Count1
Check = WorksheetFunction.Index(Key1, 1, C)
If Check = "Y" Then
Result = WorksheetFunction.Index(Res1, 1, C) & Chr(44)
Else
Result = ""
End If
Str1 = Str1 & Result
Result = ""
Next C
Str1 = Left(Str1, Len(Str1) - 1)
If Ch2 = "Y" Then Str1 = Str1 & Chr(47)
End If
If Ch2 = "Y" Then
Str2 = ""
Count2 = 0
For Each Cell In Key2
Count2 = Count2 + 1
Next Cell
For C = 1 To Count2
Check = WorksheetFunction.Index(Key2, 1, C)
If Check = "Y" Then
Result = WorksheetFunction.Index(Res2, 1, C) & Chr(44)
Else
Result = ""
End If
Str2 = Str2 & Result
Result = ""
Next C
Str2 = Left(Str2, Len(Str2) - 1)
End If
CheckKey = Name & "(" & Str1 & Str2 & ")"
End Function
```

It has several input values, they are listed below with the equivalent description and range in your sample workbook:Name As Range - The name to be included at start of final result, column A (ID)

Ch1 As Range - The cell to be checked for Key1 values, column B (Key1)

Key1 As Range - The range of cells containing Y as a result of Key1, columns C to P

Res1 As Range - The range of cells containing the result of Key1, columns C to P (1 to 14)

Ch2 As Range - The cell to be checked for Key2 values, column Q (Key2)

Key2 As Range- The range of cells containing Y as a result of Key2, columns R to V

Res2 As Range - The range of cells containing the result of Key2, columns R to V (Issue 1 to 5)

It is used in the spreadsheet like a formula with the following syntax:

=CheckKey($A2,$B2,$C2:$P2,

$A2 = Name

$B2 = Ch1

$C2:$P2 = Key1

$C$1:$P$1 = Res1

$Q2 = Ch2

$R2:$V2 = Key2

$R$1:$V$1 = Res2

Note the $ locking the row for Res1 and Res2; this will ensure the results are still taken from row 1 when the formula is copied down to more rows.

Any further questions, please let us know.

Thanks

Rob H

Trying find that extra comma..