Lookup Issue

Occasional Contributor

I have a workbook with different worksheets for each stock that I own that keeps a daily record of the price to show how it does each day of the year. At the bottom of each sheet is a formula that take the column and and averages all the current data for the year.

I also have what I call my balance sheet that pulls the daily information from all my sheets onto one location for the current day.

I was given a formula to pull the current price each day { =LOOKUP(2,1/(INDIRECT("'"&$B3&"'!B:B")<>""),INDIRECT("'"&$B3&"'!B:B")) } and put it on the balance sheet. The problem I have is that at the bottom of each sheet is a formula that does an average for the year. It turns out that the formula above will pull the average total and not the current price for the day. How do I get it to exclude the average total.


13 Replies
Any chance you can submit a sample file describing what you need.

Seems like putting a formula at the top of each sheet to give an average for the year and the latest price (by doing a SUMIFS that references the MAX of the date column) would be a simpler approach.

Would need to see how your file is laid out to give best advice




That could be

=INDEX(INDIRECT("'"&B3&"'!B:B"), COUNTA(INDIRECT("'"&B3&"'!B:B"))-1)

if I understood the logic correctly.

@Sergei Baklan 


I meant to send a redacted spreadsheet but forgot. I have attached it here

@Wyn Hopkins 


I meant to attach a redacted file but forgot. I have attached it here


Do you have XMATCH() available for your version of Excel? When it could be

=INDEX(INDIRECT("'" & $B3 &"'!B:B"), XMATCH(,INDIRECT("'" & $B3 &"'!B:B"))-1)

@Sergei Baklan I thought that the formula worked but then it did not. It came up with a $NAME?

I downloaded the file that you returned and got the message that it is in protected view and when I tell it to enable editing it pops up with the the $NAME? message.

I tried just copying the formula and pasting it in place and I still get the message.



Most probably you don't have XMATCH available for your version of Excel. On which version of Excel you are?

Protecting view - I guess you mean editing mode is not allowed, that's since you downloaded file from internet, kind of protection. Just enable it.

@Sergei Baklan I have Home and Student 2016 Version 2012 (Build 13530.20316). 

When I enable the file it immediately pops up with the error message.


I see, 2016 has no recent functionality. Please try

      INDEX(INDIRECT("'" & $B3 &"'!B:B"),
      MATCH(1,INDEX(--(INDIRECT("'" & $B3 &"'!B:B")=""),0),0)-1),

it's in column G of attached.

@Sergei Baklan OK, that worked. Thank you very much

@Bob_Lac , you are welcome


Is there a reason for not updating to the 2007 technology of Tables?

{OK, even considering such things proves that I am not a dyed-in-the-wool spreadsheet buff!]


The existing formulas with INDIRECT and whole column references caused my computer (a core17, 64 bit, Surface laptop) become sluggish (a second or so delay following any change) even on such a trivial dataset.