Hi. I am trying to create a formula to lookup data based on the name of a stock. I have Buy data of stocks that have been bought on a certain date and sell data, which shows when the shares that were bought have been sold. SALES OF STOCK ARE BOUGHT AND SOLD ON THE SAME DAY. I want the formula to lookup a symbol from the buy data, find the name (symbol) of the stock in the sell data and return the price of the stock in the row that corresponds to that symbol.

The formula I have made is as follows: vlookup(Apple,A1:F100,3,FALSE). That is the formula will lookup apple in the range a1:f100 and bring back data from the third column, which is price, in the row that it finds Apple. I copy and paste this formula in a separate column, referring to the buy data symbols for the stock as the (lookup) value, so the formula will look up symbols it finds in the buy data and search in the "sell" data range and column values.

I have a problem when applying this formula. The formula finds the stock symbol in the sell data and returns a price, but there are several shares of apple each with a different price sold on different dates. The formula automatically searches for the first Apple stock it finds and brings back the price from that day, which is NOT necessarily the same price on the day in which it was sold. The formula automatically assumes that the first sale of apple it finds is the price that I am looking for, when in fact sales of stock on different dates have different prices. For example I want a buy on January 10, which is sold on January 10, to come back with a sales price on January 10. The vlookup formula will find the first Apple stock it finds and input the corresponding price column. Is there a way to search using vlookup that will find me the price of the stock corresponding to each date? I tried using IF statements and I still cannot get the right formula. Let me know. Thanks.

This should be no problem. Could you supply us with a sample sheet. I am thinking you will need to utilize a couple different reference cells to make this work for sure but let's start with sample sheet.

Hi excelledsoftware,

I will send out the file later today as I am at work, probably around 6 or 7pm. Again I need to use vlookup to search for stock names in a "sell" table using names in the "buy" table to return the price column corresponding to the stock in the "sell" data. There are several stocks of the same name in the sell table, all on different dates with different prices. Vlookup brings back the most recent stock name with corresponding price column, which causes stocks to have the most recent price, not the price that corresponds to the date when it was sold. I need the stock corresponding to a certain buy date to have the correct sell price.

Thank you for the quick reply. Cheers!

Does the attached help?

be aware that the formula is an array formula to be committed with Ctrl+Shift+Enter

Attached Files

Example Sheet.xlsx (10.7 KB, 13 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Here is my suggestion to fix the issue you are having. See the edits I made in blue. You could write the formula in column A all the way down and then hide column A then the edited formula would work no problem.

Let me know if this fixes the issue or if you need a different route.

Thanks

Attached Files

Example Sheet edited 9-18-12.xlsx (10.3 KB, 12 views)

@excelledsoftware Any reason why you quoted my post ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

@Pecoflyer.

LOL really sorry about that I was thinking that your post was the originator. so no reason I just goofed. Sorry

Is the issue resolved with one of the attachments?

Hi excelled Software,

I just got to your post, sorry. I am getting my MBA and working full time and just looked now. Ive had no time.

I really like the simplicity of the lookup formula you added by adding a formula to convert the date into a number date from 1900 followed by a symbol to differentiate it from the other symbols. It worked well for the data I am trying to use in my excel file. Thank you!

The other formula that uses Index and Match though; I don't understand the other formula and it is not working. The formula says #N/A, and does not come up with a value. However, I can see that it could work with the logic behind it. Let me know if that second formula can be rearranged. It may be useful because I wouldn't need to create a reference column. I think your attached files have both formulas. Thanks again.

Hi Pecoflyer,

Thank you for responding. The formula appears to show me #N/A. I am not sure why it is giving me that result. The formula is good, but it is not working for some reason. Can you walk me through briefly what the formula is doing? Thanks.

Difficult to be sue without seeing your data. Did you commit the formula with Ctrl+Shift+Enter ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks. It works now

I cannot sort these dates. I separated them with text in the form date---stock symbol (11/12/2012---jonny321) using mid(MID(F2,1,FIND("---",F2,1)-1)) to only have the date in the cell. For some reason excel is not interpreting the cell as a date anymore. Is there a way for me to convert the current cells back into date form so they can be sorted?

Attached Files

Help for Sorting dates.xlsx (8.6 KB, 8 views)

Thanks. It works now

I cannot sort these dates. I separated them with text in the form date---stock symbol (11/12/2012---jonny321) using mid(MID(F2,1,FIND("---",F2,1)-1)) to only have the date in the cell. For some reason excel is not interpreting the cell as a date anymore. Is there a way for me to convert the current cells back into date form so they can be sorted?

Attached Files

Help for Sorting dates.xlsx (8.6 KB, 12 views)

To make things easy extract the dates again using All dates will be coerced to real dates

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Excellent. It works now. Just for my knowledge: Why did it not recognize them as dates with the original formula? How does multiplying by 1 convert the cell into a number that excel recognizes as a date?

Thanks