vlookup (for checkboxes) or a different formula?

Hi Ric,

VLOOKUP searches a column for a specific value, then returns the value from the cell in a different column in the same row as it finds the search value.

HLOOKUP works in the direction you want.

The formula shown below the tables is in the selected cell, A1, of the smaller table, and is filled down to the rest of the cells in that column.

Cell Table 1::F1 contains a formula whose result is the Boolean value 'TRUE', which is NOT equivalent to the text value "TRUE".

F1: 1=1

Checkboxes can have only two values, 'TRUE' if checked.or 'FALSE' if not checked.

HLOOKUP searches the top row of the lookup table for the search value 'TRUE'. If found, it returns the value from that row of the column where it found 'TRUE'. The search is from left to right, and HLOOKUP 'finds' only the first column containing 'TRUE'.

The 'permanent' 'TRUE' in the last column serves as a 'findable value' when no column before it is checked.

Here is the same table, with B1 checked. Note the results in the small table:

Additional information columns may be added at will, provided they are added to the left of the last column (currently column F). Column F can be hidden, if desired.

Regards,

Barry

Hi Ric,

Another approach using INDIRECT. As in Barry's approach I have used two tables (thanks for the HLOOKUP method, Barry!)

In the Database table, insert a row where you type the column letters.

The Result table has a blank row (Row 2) so that row numbers in the two tables match.

Cell B1 of Results is a Pop-Up Menu created from Row 1 of Database.

Cell A1 in Results creates a text reference to the chosen column in Database.

="Database::"&HLOOKUP(B$1,Database::A$1:C$2,2)

Now for the INDIRECT function.

Formula in B3 (and Fill Down)

=INDIRECT(A$1&ROW())

Tidy up by hiding Row 2 in both tables, and Column A in Results.

Choose another column from the Pop-Up Menu:

Drawback of this approach: as you add more information columns to Database, you will have to recreate the Pop-Up Menu.

Regards,

Ian.

Hi Ric,

Your last post is a good indicator of why screen shots are very helpful here. We can't see what you can see, unless you show it to us. A screenshot showing your table and the formula in the cell where it was first entered could provide a wealth of information as to what's causing the error you report and how to cure it.

My guess at the moment is that your checkboxes are in the second row of your table.

If that's the case, then your lookup table must start with the row containing the checkboxes and include all rows below that, as shown in the highlighted cells here:

Note the added header row in the large table, pushing the checkboxes down to the second row.

HLOOKUP always searches in the first row of the lookup table, so to enable searching to be done in the checkbox row, the lookup table here does NOT include row 1 of the large table.

If no extra header row has been added to the result table left version of small table), the only change needed to the forumla (shown below the tables is to edit the row references specifying the range of rows in the Lookup table (blue lozenge in the formula).

If an extra header row has been added to the result table, a second edit ( -1 ) is required in the formula, as shown in the formula above the tables. Subtracting 1 from the ROW() value adjusts the value to match the position in the Lookup table, 1 less than its position in the large table due to the first row of the large table being outside the Lookup table.

Regards,

Barry