Check box and Vlookup

Question

I have a sheet with 6 check boxes, i have the below formula that will complete a Vlookup when the check box is ticked, what i now need to know how to do is adapt the formula to complete Vlookups when several check boxes are ticked, and then put the result of the vlookups into one cell.

This formula is the current method i am using but as you will see it only works when one check box is ticked and then populates the result into the one cell.

=IF(B2=TRUE,(VLOOKUP(Sheet2!A2,Sheet3!A2:B7,2,FALSE)),"")

Thanks in advance for your help.

Answer 1

Here is an example, since there is no sample or any mockup posted, I was not able to understand which sheet has what, so I am assuming this should satisfy as per the given conditions, if not please let me know, I will try to make the necessary changes.

---

• Formula used in cell D2

=TEXTJOIN(", ",1,MAP(A2:A7,B2:B7,LAMBDA(x,y, FILTER(Sheet3!B1:B6,(Sheet3!A1:A6=x)*(y),""))))

---

• Using MAP() function to apply a custom LAMBDA() calculations for the two arrays to pass for its each element, the arrays here are A2:A7 and B2:B7 which are defined as x and y respectively.
• Within the above we are using FILTER() function to grab based on the include parameter which applies two conditions, if the names in Sheet3 is equal to the one in Sheet2 as in the example here as well as if the CheckBoxes are ticked. If these meets the conditions then returns the numbers in Sheet3 for each respective names.
• Lastly, wrapping the output within TEXTJOIN() function with a delimiter comma to get desired output.

---