Results 1 to 4 of 4

Thread: Nested IF with VLOOKUP - best solution?

  1. #1

    Nested IF with VLOOKUP - best solution?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Perhaps post a sample sheet? (no pics please)

  3. #3
    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.
    Attached Files Attached Files

  4. #4
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •