Match+Indexing within conditional formating.

[baggi]

Hello all,

I have a spread sheet with three columns, A master list, master approvals, and a checklist.

The first column is the master list of serial numbers, and the master approvals column next to it says whether or not the unit is approved (Yes, No, or blank). These two columns correspond to each other.

I want to be able to paste a short list into the third column, and have it format any cells that are not approved or blank.

I am not able to include an example spreadsheet, so I am using Columns A, B, and C corresponding to the Master list, master approvals, and the checklist, respectively. I want Column C (the checklist) to conditionally format based on the value in Column B, where the value of Column C and A are equal.

The formula that I have been trying to use within conditional formatting is:

=(INDEX($B:$B, MATCH(C2,$A:$A,0))="No") <-----for cases when the unit is not approved
=(INDEX($B:$B, MATCH(C2,$A:$A,0))="") <-----for cases when the approval is blank

I am pretty new to using more complex formulas beyond 'Sum' and 'Count', So I likely am missing something. But hey, I'm always up for learning more. Thank you in advance!

 

[JoeMo]

Welcome to the forum!

See if this works for you - adjust the ranges of cols A & B to match your data.

=OR(INDEX($B$2:$B$100,MATCH($C2,$A$2:$A$100,0))="No",INDEX($B$2:$B$100,MATCH($C2,$A$2:$A$100,0))="")

 

[baggi]

That almost works! It applies the formatting in column 'C' one block above the correct cell however. Row 1 of the sheet is the heading, Row 2 below is data; so I'm not sure why the formula as written is not working.

 

[JoeMo]

That almost works! It applies the formatting in column 'C' one block above the correct cell however. Row 1 of the sheet is the heading, Row 2 below is data; so I'm not sure why the formula as written is not working.

Did you copy the formula directly from your browser or did you re-type it? Did you select the cells you want to CF prior to setting the CF formula, being careful not to include the header cell?

Works fine for me so I'm guessing you are not applying it correctly.

 

[Peter_SSs]

I agree with JoeMo about why it is offset by 1 row. If you have a heading in row 1, you would select from C2 down and then apply the Conditional formatting given by JoeMo.

The same applies for this alternative. Select C2:Cxx then apply this formula (& adjust the $100 to be big enough to cover any expected data)

Excel Workbook
	A	B	C
1	SN	Approved	Check
2	SN1	Yes	SN5
3	SN2		SN8
4	SN3	No	SN4
5	SN4	No	SN1
6	SN5		SN2
7	SN6	Yes	SN10
8	SN7	Yes
9	SN8	Yes
10	SN9
11	SN10	No
CF Not Approved

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =VLOOKUP(C2,A$2:B$100,2,0)<>"Yes"	Abc

 

[baggi]

Did you copy the formula directly from your browser or did you re-type it? Did you select the cells you want to CF prior to setting the CF formula, being careful not to include the header cell?

Works fine for me so I'm guessing you are not applying it correctly.

I had the CF being applied to the whole column. Changing the range to one starting at the second row fixed the issue. Thank you all for your help.

 

[Peter_SSs]

I had the CF being applied to the whole column. Changing the range to one starting at the second row fixed the issue. Thank you all for your help.

Glad to contribute.

Hopefully you put a reasonable end-row on the CF as well. CF is volatile so recalculates every time anything on the sheet recalculates so can have a negative impact on sheet performance, especially if applied to hundreds of thousands of cells needlessly.