Formula to lookup an item and determine if there is a difference in similar list

[rusty_J]

I have a workbook with purchase order information with two spreadsheets. The main spreadsheet (call it sheet A) is used for notes on orders and organization. The second (B) updates from a pivot table nightly.

In (A), each row has a purchase order number, followed by the dates it is due to be shipped and received. (B) Follows the same exact format, but is sorted differently. When our orders are rewritten, the dates can change.

Basically, I need a formula that can find a purchase order number from sheet (A) on sheet (B), then look in the corresponding row to confirm if there has been a date change for that particular purchase order #.


This is for easy updating; otherwise I'll have to manually sift through 700+ purchase orders weekly to make sure the dates haven't fluctuated.


Thanks for any help you can offer!

[NBVC]

Perhaps, in a new row add a formula:

=IF(VLOOKUP(A2,'B'!A:B,2,FALSE)<>B2,"Date changed","")

where A2 and B2 on current sheet (A) contain first set to confirm

Note: You can replace "Data change" with the Vlookup formula repeated to get the actual date that has been changed.

[Richard Buttrey]

Hi,

You can use VLOOKUP() to look up a single value in the first column of a range somewhere else, and if the value exists in the first column return the value from another column on the same row that contains the value you found.

Alternatively you can use a combination of an =MATCH() function wrapped inside an =INDEX() function. Use the =MATCH to find a specific value in a column somewhere. This will return the row number that contains the value. Then use the INDEX() function specifying a range in which the value exists somewhere, with the row number you've found by the MATCH() and a column number depending on in which column the data you're looking for exists.

HTH

[rusty_J]

Beautiful! I had written a MATCH formula to give the row # originally, but was trying with hlookup. The INDEX function put it all together. Thanks a million!