Forum Discussion

Bob_Lac's avatar
Bob_Lac
Copper Contributor
Jan 06, 2021

Lookup Issue

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 ta...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 07, 2021

Bob_Lac 

That could be

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

if I understood the logic correctly.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 07, 2021

    Bob_Lac 

    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)
    • Bob_Lac's avatar
      Bob_Lac
      Copper Contributor
      Jan 14, 2021

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

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 15, 2021

        Bob_Lac 

        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.

Resources