Nested IF with VLOOKUP - best solution?

Hi All.

I was wondering if somebody would be able to help me please?

I have a sales order book to analyse on a weekly basis to get a snapshot of where we are with goods movements. I am only interested in the sales order number and changing value. So far I have used the following formula to identify which sales order number remain from the previous week with the same value: =IF(AND(E5=B5)*AND(A5=D5),"true"). This worked fine to identify where the differences immediately. Then I wrote this formula to get the values =IF(AND(A6=D6),VLOOKUP(A6,A6:B638,2,FALSE),IF(AND(A6=D6),VLOOKUP(D6,D6:E593,2,FALSE))) but the problem I have is that the sales orders are also there with matching values but not in the same rows? What I am hoping to gain from this data is to quickly see orders that are the same, new orders added, orders not shipped brought forward and any value changes due to amendments, I.E cancellations. Any help would be very much appreciated and I am able to provide the file of data if necessary. Kind regards, Shirley.

Perhaps post a sample sheet? (no pics please)

Hi Pecoflyer.


Thankyou for kindly coming back to me. I have attached the file for your review. If you look at the 'recon' sheet, this is where I have pulled out the data to compare weeks and track movements. I am eager to learn new formulas and scenario's in Excel as I want to start building some KPI and flash reports. Any help that you can give me would be very much appreciated.
Many thanks, Shirley.

Excel 2010 with free Power Query Add-In.
Compatible with Office 2013 Pro Plus and later.
No formulas, no VBA macro.
Book: "Power Query for Power BI and Excel" by Chris Webb.
http://www.mediafire.com/download/05y2qm0p7h6n1dd/10_16_15.xlsx