excel gurus question

jflail2

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

Ricardo

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.

jflail2

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.

Ricardo

Then index and match will do it.

http://www.mrexcel.com/articles/excel-vlookup-index-match.php

Ricardo

Even better; you can download an example workbook,.

http://www.contextures.com/xlFunctions03.html

Ricardo

he he; even youtube.

<object width="425" height="344"><param name="movie" value="http://www.youtube.com/v/Ue1FzZ0s9TI&hl=en_US&fs=1&"></param><param name="allowFullScreen" value="true"></param><param name="allowscriptaccess" value="always"></param><embed src="http://www.youtube.com/v/Ue1FzZ0s9TI&hl=en_US&fs=1&&quot; type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="425" height="344"></embed></object>

jflail2

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

Ricardo

Here it is how you need to set it up.

jflail2

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!!

Ricardo

I'll bill you later

jflail2

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