Free Shipping on All Orders 1-866-764-1801

Vist our Online Store
+ Reply to Thread
Results 1 to 11 of 11
  1. #1

    Member Sales Rating: (3)

    Join Date
    Feb 2007
    Posts
    2,322

    Default excel gurus question

    Ok, quick one for the excel gurus here. I vaguely remember what I'm doing here, but keep getting #names for my results, so apparently I don't remember enough.

    Basically, I have a spreadsheet with a bunch of company tickers running across the top, their historical daily dividend yields below each ticker, and then column A has the historical dates.

    I want to pull in dividend yields on certain dates in another sheet in the workbook, so I know I need to be using an index/match formula, but I can't for the life of me remember how to write one. See below. How would I write a formula to pull in PLD's dividend yield on 12/29/2009 into another sheet for example.

    For visual aid (note that all values are fake and are there for a visual aid only):

    SPG BAC PLD
    12/31/2009 4.25 2.17 6.56
    12/30/2009 4.71 2.12 6.50
    12/29/2009 4.66 2.00 6.46
    12/28/2009 4.55 2.15 6.95
    2007 Club Polk Football Pool Champ

    2010 Club Polk Fantasy Football Champ

    2011 Club Polk Football Pool Champ


    "It's like a koala bear crapped a rainbow in my brain!"

  2. #2

    Member Sales Rating: (28)

    Join Date
    Mar 2005
    Location
    Right here.
    Posts
    10,414

    Default

    Several ways; one of them would be to use vlookup; in this case assuming the dates are in column A, use the vlookup searching for the date, range would be A : D, and look in column 4.
    _________________________________________________
    ***\\\\\........................... My Audio Journey ............................./////***

    2008 & 2010 Football Pool WINNER
    SOPA

    Thank God for different opinions. Imagine the world if we all wanted the same woman

  3. #3

    Member Sales Rating: (3)

    Join Date
    Feb 2007
    Posts
    2,322

    Default

    The problem is, I have about 70 tickers in 1 sheet, so I'd have to know which column each ticker was in. That's why I was shooting for an index/match function instead of a vlookup, and it would be dynamic if tickers move around the main sheet, or a new company gets added.

    Thanks though Ricardo, if I didn't have a ton of data (10 years worth of daily dividend yields and 70 companies) that might work.
    2007 Club Polk Football Pool Champ

    2010 Club Polk Fantasy Football Champ

    2011 Club Polk Football Pool Champ


    "It's like a koala bear crapped a rainbow in my brain!"

  4. #4

    Member Sales Rating: (28)

    Join Date
    Mar 2005
    Location
    Right here.
    Posts
    10,414
    _________________________________________________
    ***\\\\\........................... My Audio Journey ............................./////***

    2008 & 2010 Football Pool WINNER
    SOPA

    Thank God for different opinions. Imagine the world if we all wanted the same woman

  5. #5

    Member Sales Rating: (28)

    Join Date
    Mar 2005
    Location
    Right here.
    Posts
    10,414

    Default

    Even better; you can download an example workbook,.

    http://www.contextures.com/xlFunctions03.html
    _________________________________________________
    ***\\\\\........................... My Audio Journey ............................./////***

    2008 & 2010 Football Pool WINNER
    SOPA

    Thank God for different opinions. Imagine the world if we all wanted the same woman

  6. #6

    Member Sales Rating: (28)

    Join Date
    Mar 2005
    Location
    Right here.
    Posts
    10,414

    Default

    he he; even youtube.

    _________________________________________________
    ***\\\\\........................... My Audio Journey ............................./////***

    2008 & 2010 Football Pool WINNER
    SOPA

    Thank God for different opinions. Imagine the world if we all wanted the same woman

  7. #7

    Member Sales Rating: (3)

    Join Date
    Feb 2007
    Posts
    2,322

    Default

    Haha, thanks for the help (many times) Ricardo! No Youtube access at work, and this is not something I need to fess up to forgetting how to do.....my excel basics are failing me...
    2007 Club Polk Football Pool Champ

    2010 Club Polk Fantasy Football Champ

    2011 Club Polk Football Pool Champ


    "It's like a koala bear crapped a rainbow in my brain!"

  8. #8

    Member Sales Rating: (28)

    Join Date
    Mar 2005
    Location
    Right here.
    Posts
    10,414

    Default

    Here it is how you need to set it up.
    Attached Files
    _________________________________________________
    ***\\\\\........................... My Audio Journey ............................./////***

    2008 & 2010 Football Pool WINNER
    SOPA

    Thank God for different opinions. Imagine the world if we all wanted the same woman

  9. #9

    Member Sales Rating: (3)

    Join Date
    Feb 2007
    Posts
    2,322

    Default

    Now that's EXACTLY what I needed. You are the man Ricardo. I wasn't naming my match ranges properly, so it was breaking the whole formula. Thank you sir, you just saved me another hour of headache!!
    2007 Club Polk Football Pool Champ

    2010 Club Polk Fantasy Football Champ

    2011 Club Polk Football Pool Champ


    "It's like a koala bear crapped a rainbow in my brain!"

  10. #10

    Member Sales Rating: (28)

    Join Date
    Mar 2005
    Location
    Right here.
    Posts
    10,414

    Default

    I'll bill you later ;)
    _________________________________________________
    ***\\\\\........................... My Audio Journey ............................./////***

    2008 & 2010 Football Pool WINNER
    SOPA

    Thank God for different opinions. Imagine the world if we all wanted the same woman

  11. #11

    Member Sales Rating: (3)

    Join Date
    Feb 2007
    Posts
    2,322

    Default

    Haha, as long as you're accepting used PS3 games I think we're good to go :)

    Seriously, thanks a ton. Client is happy= my job is better off....
    2007 Club Polk Football Pool Champ

    2010 Club Polk Fantasy Football Champ

    2011 Club Polk Football Pool Champ


    "It's like a koala bear crapped a rainbow in my brain!"

Thread Information

Users Browsing this Thread

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

     

Similar Threads

  1. M$ Excel Help please
    By avelanchefan in forum The Clubhouse
    Replies: 6
    Last Post: 08-14-2009, 12:06 PM
  2. OK DVD gurus..yet another question...
    By steveinaz in forum Electronics
    Replies: 9
    Last Post: 01-26-2008, 08:51 AM
  3. excel charting question
    By jflail2 in forum The Clubhouse
    Replies: 3
    Last Post: 01-01-2008, 11:28 AM
  4. Nerd Question: FFT's in Excel
    By jdhdiggs in forum The Clubhouse
    Replies: 6
    Last Post: 02-19-2007, 07:48 PM
  5. Denon DVD gurus...I have a question
    By steveinaz in forum Electronics
    Replies: 6
    Last Post: 03-10-2006, 11:58 AM

Posting Permissions

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