excel - VLOOKUP based conditional formatting highlights the cell above the intended one -
i'm trying apply conditional formatting column in excel using "use formula determine cells format" feature.
in sheet 1 have list of names in column corresponding reference numbers in column b. in sheet 2 have longer list of reference numbers in column c. want conditional formatting rule reference numbers in sheet 1 against in sheet 2 , highlight cell overlap.
i have found apparent solution using vlookup formula:
=$b2=vlookup($b2,sheet2!$c:$c,1,false)
this works in cells in sheet 1 have correct formatting applied, cells have been shifted 1 (image). have copied highlighted reference numbers out , done lookup against reference numbers in sheet 2 double-check , top 1 highlighted isn't in sheet 2 1 below filled cells (image).
i have tried replacing vlookup in conditional formatting index/match function in case there problem vlookup result same: =$b2=index(sheet2!$c:$c,match($b2,sheet2!$c:$c,0))
i've tried removing spaces between xx , numbers in references based on different stackoverflow answer, no luck. i've tried changing format of cells general text same result.
any ideas appreciated!
you created on entire column b applies to: b:b. formula references b2 relatively referenced b1. highlights applied cell 1 row below. here solutions:
- change applies to:
=$b$2:index($b:$b, match("zzz", $a:$a))
. resolve correct range , not stay formula. need have @ formula after apply ensure htexla1
references have not adjusted teh new range. - change formula ignore first row. e.g.
=and(row()>1, isnumber(match($b1, sheet2!$c:$c, 0)))
. no further adjustment should necessary , fuill column applies to: valid..
Comments
Post a Comment