VBA VLOOKUP with Multiple Criteria

[hemal89]

Hi all,

I am having some problems getting the vba right for this vlookup function. I will breakdown my request below to aid understanding of what I am trying to achieve.

- 2 sheets in workbook
- 1st sheet contains core data
- 2nd sheet holds referenece data

- Sheet 1 columns D:J contain decimal values from rown 2 downto row 143
- Sheet 2 contains the reference values in column A row 2 to row 168

- In Sheet 1 column M I would like to be conditionally formatted either red or green depending on if any of the values within D:J match the values of column A in sheet 2.

Hope that makes sence,

All help provided will be much appreciated,

Many Thanks,

H

[jonesa01]

Hello,

It is quite easy you need to create two different conditional formats:

Choose "Use a formula to determine which cells to format"

Now all you need is to use the match function. So for the green the first cell with the formatting would be:

=MATCH(D1:J1,Sheet2!$A:$A,0)<>0

And for the red (no match)

=IsNa(MATCH(D1:J1,Sheet2!$A:$A,0))

This can then be copied to other rows and excel will automatically change the row numbers.

Thanks,

Adam