+ Reply to Thread
Results 1 to 9 of 9

can't get a formula which uses multiple VLOOKUPs to work

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    can't get a formula which uses multiple VLOOKUPs to work

    I've attached a workbook which has me stumped on a formula which uses an "IF" statement followed by 5 VLOOKUP formulas.

    The actual workbook I'm using has thousands of rows and thousands of "orders" where i need to obtain the profit/loss for each order number.

    You'll see the problematic formula in cell U11, along with a written explanation of what it is supposed to do.

    I wonder if I've incorrectly nested the formulas... or if the prob is elsewhere... or if there's a better solution than all these VLOOKPs!

    Please let me know if you have any questions.

    I appreciate your help. And stay safe!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: can't get a formula which uses multiple VLOOKUPs to work

    How about
    =IF(N11="close",IF(VLOOKUP(D11,D9:N10,11,TRUE)="buy",F11-VLOOKUP(D11,D9:F10,3,TRUE),VLOOKUP(D11,D9:F10,3,TRUE)-F11),0)

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: can't get a formula which uses multiple VLOOKUPs to work

    Hi jrtaylor,

    If you keep the Stock Symbol or Cusip number with your data, it would be a lot easier. Then you could do a pivot table or a running balance or lots of other things with your data. What happens if you buy 40 but only sell 30 or buy 40 and another 30 at a different price and then sell 50?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: can't get a formula which uses multiple VLOOKUPs to work

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Also, unless your "Order" numbers are always in accending order, you want to replace the TRUEs with FALSE.
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: can't get a formula which uses multiple VLOOKUPs to work

    Thanks Fluff. This works!
    Last edited by jeffreybrown; 03-25-2020 at 01:04 PM. Reason: Please avoid full quotes!

  6. #6
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: can't get a formula which uses multiple VLOOKUPs to work

    Thanks Chemist, this also works, and yes I'll keep in mind the order of order numbers!
    Last edited by jeffreybrown; 03-25-2020 at 01:03 PM. Reason: Please avoid full quotes!

  7. #7
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: can't get a formula which uses multiple VLOOKUPs to work

    Hi Marvin, thanks. The workbook's other columns already contain this data (which is all imported). The issue I'm dealing with is that I need to determine P/L based on price changes rather than order sizes, and thus the need for the formula in column U. By obtaining the "pure" PL via price differences regardless of order size I can do some further analysis I need to do.
    Last edited by jeffreybrown; 03-25-2020 at 01:03 PM. Reason: Please avoid full quotes!

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: can't get a formula which uses multiple VLOOKUPs to work

    You're welcome & thanks for the feedback

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: can't get a formula which uses multiple VLOOKUPs to work

    Hi jrtaylor,

    Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
    HTH
    Regards, Jeff

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multiple VLookups in on formula
    By superchew in forum Excel General
    Replies: 14
    Last Post: 12-25-2014, 03:52 PM
  2. Formula with multiple IFs and VLOOKUPS, trying to add an OR!
    By drewan187 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2014, 09:17 AM
  3. Formula with multiple IFs and VLOOKUPS, trying to add an OR!
    By drewan187 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-04-2014, 06:39 AM
  4. Formula with multiple IFs and VLOOKUPS, trying to add an OR!
    By drewan187 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-04-2014, 06:26 AM
  5. [SOLVED] Multiple vlookup formula won't work if expanded to 4 vlookups
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2014, 03:32 PM
  6. vlookups wont work as multiple instances, what will?
    By oneblondebrow in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-05-2013, 11:12 AM
  7. Multiple Criteria with VLookups/Indexs - Won't work?
    By zyrakris in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-30-2013, 12:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1