VLOOKUP/comparing adjoining cells in seperate sheets

[HUSDTECH]

I have a workbook with two Sheets (sheet1 and sheet2) that each have 2 columns of data. Column A is a unique ID and column B is a date. I need to do a vlookup for a matching ID in column A and then compare column B values. If sheet2 column b is < sheet1 column b then I need to update sheet1 column b with the value from sheet2 column b. If sheet1 column b is not less than sheet2 column b or not present then the value needs to be unchanged.

Any help would be greatly appreciated.

 

[BlueHornet]

Welcome to the board.

The best way that I know of to do this - in fact, the only way that I know how to do it - involves a few easy-to-follow steps.

1. Start from the worksheet that you have decided is going to be your "Master" sheet. Make a copy of it so that your later data changes don't mess up anything.

2. In the C or D column of your Master worksheet, perform the VLOOKUP of the A value on that sheet into the second sheet to get the applicable date from the second sheet (if a date exists, and if the A value also exists on that sheet).

3. After your VLOOKUP is done and you now have three columns of data: the Items in A and their dates (the original two-column Master sheet) plus a column of dates from the VLOOKUP.

4. Write a formula in another column to the right to compare your "Master" dates against your VLOOKUP dates and resolve "which one wins" based on your rules. (Be sure to consider that there may be Items on your Master list that don't exist on your secondary sheet - and vice versa - and be sure to consider that some dates may have been entered incorrectly on either list - as text, for example, and not numbers in date format - or dates may be missing on one list or the other, etc. In other words, examine your data to check for various error conditions.)

5. When you are sure that you have a good list of Master items (Column A) and "winning" date values to apply in the far right column of your worksheet, copy those winning dates and do a "Paste Special / VALUES" onto your original Column B, check the results, wipe out your added columns, and you're done.

 

[HUSDTECH]

Yeah, that's pretty much what I had to do.

The first function I used performed a look up between sheets and if a value was present it was inserted into column c. If a match was not found, a blank was returned instead of an error code.

=IFERROR(VLOOKUP(A1,Sheet2!$A$1:$B$220,2,FALSE),"")

In column D I used this function which compared the value of column C that was just created to the value in column B. If C was older than B, C was used. If B was older than C (or C was not present) then B was used.

=IF(B1>C1,C1,B1)

Then copy and paste values and delete the extra columns.

Thanks for your time.