Finding Occurences of "True" and returning the Correlating Description

[braydon16]

I'm working on a survey and have two sheets. The first allows the user to select items using checkboxes. These checkboxes are in column D. The resulting True/False is linked to column E. The user is instructed to only select 3 items from the survey checkboxes, so, hopefully, only 3 "True" results will be in column E and the rest False.

On the second sheet, the results are limited to 3 items. I need to construct a formula that looks for the first True occurrence on the first sheet and returns the description (in column B of the first sheet) to this second sheet. It will also do the same for the subsequent 2nd and 3rd items.

[tigeravatar]

braydon16,

I created a UDF named OLOOKUP (for OccurrenceLookup) which has the following format:
=OLOOKUP(LookupValue, SourceList, ReturnList, OccurrenceIndex)

Notes:

• LookupValue is the criteria you're searching for. In this case, it would be "true"
  • This is not case sensitive
• SourceList is the range of cells that contains the criteria. In this case, it would be the range of cells in column E of Sheet1 (containing True and False for the checkboxes)
• ReturnList is the range of cells that contain the values you want returned. In this case, it would be the range of cells in column B of Sheet1 (the description)
• OccurrenceIndex is which number in the occurrences you're looking for. So if you need the result of the second occurrence, this should be 2

I have attached a sample workbook based on your post so you can see how it works.

Please Login or Register  to view this content.

To add a UDF to a workbook:

1. Save a copy of the Excel workbook you want to modify
2. Always test macros in a copy so that the original is preserved in case the modifications don't go smoothly
3. Open the copy of the Excel workbook you want to modify
4. Use the keyboard shortcut ALT+F11 to open the Visual Basic Editor
5. Insert -> Module
6. Copy/Paste the code into that area

To use a UDF in a workbook, simply type it into a cell like you would any other Excel formula. In the sample workbook, I have attached, the formula in the Results page, cell A2 is:
=OLOOKUP("true",'TrueFalse Items'!$E$2:$E$11,'TrueFalse Items'!$B$2:$B$11,1)

Hope this helps,
~tigeravatar