I have two positive values in columns A1:B1, and a small table on D1:H2 on the same spreadsheet. I'd like to use a formula to conditionally highlight the values of A1:B1 on the table (if they're present). I tried to do this using match, and hlookup, but I couldn't make it work. I'd be glad if you could give me a little help.
sample data:
A : B D E F G H
1 5 1 3 5 7 9
2 4 6 8 10
So the result'd be like this with conditional formatting:
You can set the conditional format custom formula to this:
=MATCH(D1,$A$1:$B$1,0)
(assuming the table you're formatting starts at cell D1). There's no need for HLOOKUP.
In a conditional format custom formula, a relative cell reference (no $ signs) will be replaced for each cell in the formatted range. So the D1 here basically means "the current cell."
We're trying to match the current cell to any cell in the lookup table, so we just reference the whole range of the lookup table ($A$1:$B:$1). The absolute reference ($'s) makes it so that this reference is always the same, even as the conditional format rule iterates over the range of formatted cells.
