Excel match columns from different sheets

1
votes

Trying to work with some excel here.. And can not get my head around how to do stuff.

I have two sheets with data. Sheet1 consists of :

  A     B     C
User  UserID Area

Sheet2 consists of :

  A  
UserID

Sheet1:B may consist of the same userid on multiple rows. Sheet2:A has an ordered list without duplicated userid's

I would like to get the Sheet2:A userid matched against Sheet1:B. If matched, insert Sheet2:B userid into Sheet1:D (new column) on the matched row If above matched, and Area = 2, mark row with Color red.

Kind Regards Lars

1
excel
You want to do that with formulas on sheet or VBA? – Dawid SA Tokyo
Formulas. I have even less experience with VBA ;-) – osomanden

1 Answers

0
votes

In column D on sheet1 add this
=IF(IFERROR(MATCH(B2,'Sheet2'!A:A,0),"")<>"",B2,"")

Then select all cells, then Conditional Formatting > New Rule > Use a formula... > =AND($D1<>"",$C1=2) and select your color as required. Note that your conditional formula is using multiple criteria.

Here is more about Conditional Formatting: Multiple conditional formatting rules across multiple ranges?