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.