MATCH Function

[rge0036]

Using the MATCH function, I want the lookup_value to be a blank cell. I have tried =MATCH(,O18:O72,0) and =MATCH("",O18:O72,0) but neither work. How do I set the lookup_value to be a blank??<!-- google_ad_section_end -->

 

[jeffreybrown]

Hello & Welcome to the board,

Can you post a small screen shot of what you are trying to do?

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

 

[Haseeb Avarakkan]

Try,

=MATCH(TRUE,INDEX(O18:O72="",),0)

 

[rge0036]

Jeff,
Thanks for replying. It will be easier if I briefly describe the issue rather than a screen shot.

I am trying to keep track of the change in a stock price during the year. So in Column A are dates starting on Jan 1, 2011 and the last date is Dec 31, 2011. In column B is the stock price. About every other day, I insert a row above Dec 31 with the new date and new stock price for that date. Of course, the stock price for Dec 31, 2011 is blank. So the columns might look as follows:

1/1 $71.35
1/3 $72.46
1/4 $73.81
12/31

After inserting a new date and price, I want the spreadsheet to automatically calculate the dollar difference between the new stock price and the price on 1/1 ($71.35). I am currently using =INDEX(B1:B4,MATCH(0,B1:B4,0)-1)-B1. But this requires that I put a stock price of $0.0 after Dec 31 which screws up my plot. How can I set the look_up value in the MATCH function to look for a blank (on Dec 31) rather than have to add $0.0 on Dec 31?

Hello & Welcome to the board,

Can you post a small screen shot of what you are trying to do?

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

 

[rge0036]

Haseeb,
Thanks for the reply. I tried this but got an error message back. Could you double check your solution and let me know if there might be an alternative?

Bob.

Try,

=MATCH(TRUE,INDEX(O18:O72="",),0)

 

[Scott Huish]

Why are you looking for the blank, why not look for the last value, it's much easier.

=LOOKUP(9.9999999999E+307,B:B)-B1

 

[rge0036]

This is a MUCH better solution than what I was trying. It does exactly what I wanted. Thanks. And thanks to Jeff and Haseem for replying as well.

Bob.

Why are you looking for the blank, why not look for the last value, it's much easier.

=LOOKUP(9.9999999999E+307,B:B)-B1