Index and Match with muliple conditions

Hi Tim,

You wrote:

"In the table below "Office Entry Worksheet - Area 1" I am trying to insert a formula in Column B that will result in a "True or False" answer and be used for conditional highlighting"

As I read this, Column B will contain a series of values that includes only TRUE or FALSE. No other value would appear in any cell in column B.

Conditional Highlighting rules depend on the comparison of the value in a cell with another value either written into the rule, or found in another cell. What cell is to receive the conditional highlighting? What values will that cell hold?

"The first condition that has to be met is that the checkbox in Table "Labor and Material Checklist" Column A must be check, if it is not checked, then move to the next row. If the box is checked, then the next condition would need to index and match column B with Column C of table "Office Entry Worksheet - Area 1". If there is a match in Column C, then move to the next row of column B in the Table "Labor and Material Checklist". Continue this until there is a "no match", then highlight Column B of Table "Office Entry Worksheet - Area 1" in red (or something like "MISSING TASK")"

Formulas do not "move to the next row" dependent on the result of a previous formula. Each formula does its job independently of the others, although its result may depend on the results calculated by other formulas.

I think I have an approximate understanding of what you are trying to accomplish here, but not an exact one.

In the example shown, you have three items checkec in the lower table, "Labor and Material Checklist"

In the upper table, "Area 1..." there is:

• an empty row (row 2) (why?)
• The label Area 1 in cell C3, and an 'empty' cell (D3), matching B2 and C2 in the first checked row of the lower table. Are these entered values, or to be constructed by formula?
• The label "Plans and Permits" in cell C4 and "Project planning...purchasing" (in red) in D4, matching B3 and C3 (disregarding colour) in the second checked row of the lower table. Same questions as above, plus.. Is the colour an example o the conditional highlighting mentioned above? If so, what condition has caused this text to be coloured?
• A row of empty cells (row 5) despite the presence of a checked box on row 4 of the lower table. Why?

Regards,

Barry

The purpose of the formula and conditional highlighting to is make sure that no phase that is "check marked" on Table "Labor and Material Checklist" (lower screenshot) is forgotten.

Would something like this work to achieve that purpose?

I put the check in the first table instead. The formula in D2 of my example, filled down the column, is:

=IF(AND(A2=TRUE,COUNTIFS(Worksheet::B,B)<1),"missing","")

If the checkbox in column A is checked and it counts no occurrences of the contents of column B in column B of the Worksheet table the formula inserts the word "missing." Otherwise, it leaves the cell blank.

I set Conditional Highlighting for the cells in column D like this, turning a the text red if the cell has something in it.

SG

Hi Tim,

Assuming the the Conditional Highlighting is the "red text" in column E of Office Entry..., under what condition does this formatting apply? In the example ALL cells in this column containing text have "red text".

MATCH will find only the first match to its search-for argument. Your example shows three rows with Electrical in column D of Office Entry.... Is this correct?

Formula: Office Entry::B4:

This formula returns a warning: The formula uses a Boolean in place of a number

And an Error: MATCH couldn’t find the value “1”.

Both relate to the use of "1" to mean "TRUE"

=INDEX(Labor and Material Checklist::A:B,MATCH(1,(Labor and Material Checklist::A=TRUE)×(Labor and Material Checklist::B=D),0))

Syntax: INDEX(range, row-index, column-index, area-index)

range: Labor and Material Checklist::A:B --columns A and B of the table named Labor and Material Checklist

row-index: MATCH(1,(Labor and Material Checklist::A=TRUE)×(Labor and Material Checklist::B=D) --result from function. see below.

column-index: 0 --fixed value. Will cause an error. The range being indexed has two columns. Column 1 is column A, column 2 is column B. There is no column zero.

area-index: omitted --as the index applies to a single range, the optional area-index value may be omitted.

Syntax: MATCH(search-for, search-where, matching-method)

search-for: 1 --a numerical value. Will never be found if MATCH is looking in a column containing only Boolean values, or containing only text.

search-where: multiplication statement multiplying Boolean values -- causes the error message shown above.

matching-method: omitted --may be set to 1 (largest value less than or equal to search-for value), 0 (exact-match), or -1 (smallest value greater than or equal to search-for value). If ommited, 1 is assumed.

Even if this multiplication worked, I can't see how the result would tell MATCH where to look for a match to the value 1.

More below.

Regards,

Barry

Part 2:

What is the purpose of the checkboxes in column A of Office Entry...?

If I've read correctly, you want the formula in column B of Office Entry... to return either TRUE or FALSE.

What is to determine this result? What is to happen in the table is the result is TRUE? What is to happen in the table is the result is FALSE?

Column C of Office entry... appears to contain the same pop-up menus as column D of the same table. What is the purpose of this column?

Regards,

Barry

PS: In response to you question above: Yes the link works.

B

So the overall purpose is to determine that

• all the items in column B of ...Checklist
• that are on rows which have checked boxes in column A of that table,
• are also listed in column D of Office Entry...,
• and if they are not, to flood column C of Office Entry... with red.

Note: Office Entry... shows "Electrical" entered on three rows. Is that correct? Will it ever happen? If so, you will need a distinct label for each occurrence.

Question: Assuming one or more of the checked items on ...Checklist were missing from Office Entry..., how would it be known which items this/these was/were?

Here's a possible solution, requiring an additional column (B) on ...Checklist. This column may be hidden.

Column B contins a formula that creates the index shown—a serial number for each checked box in column A—to be used by VLOOKUP in a formula on Office Entry...

B2, and filled down to the end of column B:

=IF(A,MAX(B$1:B1)+1,"")

Office Entry has two formulas:

B4, and filled down to the end of column B:

=IF(ROW()−3>MAX(Labor and Material Checklist::$B),"",IF(ISERROR(MATCH(VLOOKUP(ROW()−3,Labor and Material Checklist::$B:$C,2,0),$D,0)),1,0))

The core formula is the part in bold.

The first IF is a switch that checks the maximum value in the index column, and cancels the core formula if the last indexed row has been processed.

Working from the inside outwards, the core formula does this:

• ROW()-3 returns the current index numbern ad hands it to VLOOKUP
• VLOOKUP searches for that number in the leftmost column of the lookup table, columns B and C of ...checklist, and returns the value from the same row of column 2 of the lookup table, passing that value to MATCH.
  
• MATCH searches for the returned value in column D (of 'this table') and, if it is found, returns a number indicating its position in the list that is column D to ISERROR.
• ISERROR recognizes the number as a non-error value, and returns FALSE to IF.
• IF receives FALSE, and returns the 'if FALSE' value, 0, to the cell containing the formula.
  
• If MATCH does not find an exact match for the value returned by VLOOKUP, MATCH returns an error message to IFERROR.
• IFERROR returns TRUE to IF
• IF receives TRUE and returns the if TRUE value, 1, to the cell containing the formula.

C1 is the key to the conditional highlighting rule for the cells in the rest of column C.

C1: =SUM(B)

As long as every cell in column B contains a zero, or contains a null string, C1 will contain zero. As soon as any cell in column B contains a 1, C1 will contain a value that is greater than zero.

Conditional highlighting rule:

Conditional highlighting rules change the format of the cell containing the rule. The rule compares the value in its cell to a fixed value, or to the value in another cell. All of the cells in column C are empty, which, considered as a number, evaluates as zero.

Each of the cells in column C, starting at C4, has the default fill setting (No fill) as its regular state, and each contains the same rule for conditional highlighting:

If the number in this cell is less than the number in $C$1, fill this cell with red.

If one of the checked rows on ...Checklist is missing from Office Entry..., the result is as shown below:

Regards,

Barry

"the user would know that all the check marked items from Checklist Column B are present (at least once) in Column D of Office Entry..."

It may be addressed in the column at the right side of the table, but that's not where VLOOKUP is getting the values to pass to MATCH.

Regards,

Barry

"That has worked brilliantly!!! "

Except for this detail:

"the user would know that all the check marked items from Checklist Column B are present (at least once) in Column D of Office Entry..."

True, but the "items from column B" and the "items in column D" are just the category names for the phases. If there are three entries in column D,, each for a different set of tasks, but all falling into the same "area/phase" category, as there are for "Electrical" in the example, the formula will not differentiate between all three entries being there, only one entry present, or more than three entries being there.

Is that an issue?

Regards,

Barry