I'm looking for a way to compare a text string or cell address to a column of possible matches. Then I want to concatenate notes from every row that has a match, but only if a logical test is true. A UDF would be perfect.
I've seen one from Rick Rothstein mentioned a couple times now, but I can't figure out how to add the logical test that I require. In the table below is an example of what I'm looking for. The values in column A (Item) are irrelevant for my purposes, but I want to compare their cell addresses (text strings) against a lookup table in D1:F7. Column B is an example of the results I'm looking for. Note that item $A$2 is present in D3, but the logical test in E3 returns FALSE so B2 is empty. $A$3 is present in D2 and D5. E2 is TRUE and E5 is FALSE, so only F2 is returned in B3. I hope this makes sense.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Results[/TD]
[TD][/TD]
[TD]Cell References[/TD]
[TD]Logical Test[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]$A$3, $A$5[/TD]
[TD]TRUE[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]AA[/TD]
[TD][/TD]
[TD]$A$2[/TD]
[TD]FALSE[/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]EE[/TD]
[TD][/TD]
[TD]$A$6[/TD]
[TD]FALSE[/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]AA[/TD]
[TD][/TD]
[TD]$A$3[/TD]
[TD]FALSE[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]EE[/TD]
[TD][/TD]
[TD]$A$4, $A$6, $A$7[/TD]
[TD]TRUE[/TD]
[TD]EE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]EE, FF[/TD]
[TD][/TD]
[TD]$A$7[/TD]
[TD]TRUE[/TD]
[TD]FF[/TD]
[/TR]
</tbody>[/TABLE]
I've seen one from Rick Rothstein mentioned a couple times now, but I can't figure out how to add the logical test that I require. In the table below is an example of what I'm looking for. The values in column A (Item) are irrelevant for my purposes, but I want to compare their cell addresses (text strings) against a lookup table in D1:F7. Column B is an example of the results I'm looking for. Note that item $A$2 is present in D3, but the logical test in E3 returns FALSE so B2 is empty. $A$3 is present in D2 and D5. E2 is TRUE and E5 is FALSE, so only F2 is returned in B3. I hope this makes sense.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Results[/TD]
[TD][/TD]
[TD]Cell References[/TD]
[TD]Logical Test[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]$A$3, $A$5[/TD]
[TD]TRUE[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]AA[/TD]
[TD][/TD]
[TD]$A$2[/TD]
[TD]FALSE[/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]EE[/TD]
[TD][/TD]
[TD]$A$6[/TD]
[TD]FALSE[/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]AA[/TD]
[TD][/TD]
[TD]$A$3[/TD]
[TD]FALSE[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]EE[/TD]
[TD][/TD]
[TD]$A$4, $A$6, $A$7[/TD]
[TD]TRUE[/TD]
[TD]EE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]EE, FF[/TD]
[TD][/TD]
[TD]$A$7[/TD]
[TD]TRUE[/TD]
[TD]FF[/TD]
[/TR]
</tbody>[/TABLE]