Matching to multiple vertical columns

[BhavanT]

Hello,

Hope all are doing well.

Basically, I'm looking to get "Y" or "N" in sheet1 column B for any changes in sheet2 to in columns F and G based on Colum E. I use the formula below, but getting the same results for all other employees hasn't had state change.

=IF(INDEX(Sheet2!E:E,MATCH(1,COUNTIF($B$1,Sheet2!E:E),0))=$B$1,IF(VLOOKUP($A2,Sheet2!B:F,5,FALSE)=VLOOKUP($A2,Sheet2!B:G,6,0),"N","Y"),"N")

Thank you for the help
Bhavan

[Bernie Deitrick]

You should post a small example workbook with two sheets filled with small example data, with a column of Y and N values based on what you want, with some explanation of your reasoning.

[cubangt]

When you copy that formula down, does your match reference change accordingly? If not then you need to make sure that your cell reference is changing based on the row.

[BhavanT]

Thank you Bernie!
I'm having a hard time attaching the file on my post, is any different ways to sent attachment through the forum?

[Bernie Deitrick]

Instructions for attaching workbooks are at the top of every page so make sure you follow them. But if you haven't posted enough, you may not have permission - in that case, you can copy tables from the sheets and insert the tables into the message body. Just describe the layout and sheet names.

[BhavanT]

Hi Bernie,

In columns F and G in sheet2, you will see the old value and new value, which highlight changes, I would like to handpick few important changes like state or salary, etc based on column E or D which then match against employees names in Sheet1.
Sample file attached
Thank you for your help
Bhavan

[BhavanT]

No, that's where I'm confused.

[cubangt]

Can you provide in your sample, some rows with the expected results.. since that formula isnt returning what you want, can you manually just populate the cells with the values you expect to see

[Bernie Deitrick]

Employee A had a change of State value, but Employee B did not. So why do you have "Y" for Employee B?

[BhavanT]

Hi Bernie,

That's where the problem is. I don't understand why "Y" is appearing on employee B, something I'm trying to resolve.

Thank you
Bhavan

[JeteMc]

Not sure that I totally understand, however the following yields "Y" for Employee A and "N" for Employee B in cells B2:B3 respectively:

Please Login or Register  to view this content.

Note that in my opinion the use of whole column references makes troubleshooting a formula very difficult.
Let us know if you have any questions.