Results 1 to 7 of 7

Thread: help with impossible formula

  1. #1

    help with impossible formula



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

    please help! here are the two formulas i would like to combine. both work standing alone, but i need them both to be in the same cell if that is even possible.

    =SUMIF(Sheet2!B$2:B$20,"="&A18,Sheet2!D220)

    =LOOKUP(2,1/(D220<>""),D220)


    further explanation, (see attached workbook) when AMD is entered into A18 the result in cell d18 should be 5.00. when a ticker is entered that matches an entry in sheet2 then the latest data entry from sheet2 cell range d2:d20 should be pulled into the appropriate d column cell in sheet1. sheet2 is continually being added toi do not want a SUM, but only the latest corresponding price.
    Thanks in advance if anyone can help
    Attached Files Attached Files

  2. #2
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    I've not looked at your example yet but when you say you want them both in the same cell what do you mean, do you want the result of the SUM then the result of the vlookup or do they need to calculate with each other?
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  3. #3
    they need to calculate with each other. i am not looking for a SUM so the first formula is obviously wrong. that's why i assume a LOOKUP formula is also needed to refine the results. i want to enter a ticker in sheet1 that matches an entry in sheet2 then the latest price entry from sheet2 cell range d2:d20 should be pulled into the appropriate d column cell in sheet1. sheet2 is continually being added to

  4. #4
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    in C18 =VLOOKUP(Sheet1!A18,Sheet2!B611,2,FALSE)
    In D18 =VLOOKUP(Sheet1!A18,Sheet2!B611,3,FALSE)

    Then copy down!
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  5. #5
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    Ah!, just noticed a problem, you have two AMD's VLookup will always return the first result, how will you be able to distinguish between teh two AMD results and which one you woill want?
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  6. #6

    Solved

    THANKS to Simon Lloyd for your help and replies! i received this formula from another forum that did the trick.


    =LOOKUP(2,1/(Sheet2!$B$1:$B$1000=Sheet1!A18),Sheet2!$D$1:$D$1000)

    Thanks for spending the time to help.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Next time, please post the link to the other forum at the beginning of the process. Here's why: http://www.excelguru.ca/forums/faq.php?faq=crossposting
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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