Forum Discussion

ccivitella01's avatar
ccivitella01
Copper Contributor
Feb 05, 2021

Vlookup help

I have a spreadsheet where I need to lookup a value based on a drop down menu that changes for the period and year.   Variance Tab Data Tab I am trying to get a return value of ($23,3...
excel
Formulas and Functions
ccivitella01's avatar
ccivitella01
Copper Contributor
Mar 10, 2021
Thanks for the information. i will try it.
ccivitella01's avatar
ccivitella01
Copper Contributor
Mar 10, 2021

ccivitella01 

When I tried Hans solution I got a #VALUE! error.  When I tried JMB17 solution, it did not return any results.  I tried the formula in cell D18 on the Monthly Variance Report tab.  

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Mar 10, 2021

    I'll try to explain a little better. Some of excel's functions, like index, offset, xlookup, indirect, are capable of returning data 'by value' or 'by reference'. Meaning, it can return the value of a cell or it can return the cell (as a range object).

    So, the index function could be used in the same manner as you would use a range reference.
    Instead of writing SUM(D2:D6), you could have =SUM(INDEX(D2:D23,1):INDEX(D2:D23,5)).

    In the version I posted, you can see it is testing the typename of the argument passed to it and, if it is a 'Range' (the function passing information to it is capable of passing by reference - index, xlookup, offset, indirect, or even just plain cell reference), then it copies the comment from that cell to the cell calling it and also returns the cell value. Otherwise, if arg is not a range, then it just returns whatever value was passed to it (presumably an error, like #N/A).

    • ccivitella01's avatar
      ccivitella01
      Copper Contributor
      Mar 11, 2021
      I moved the IFERROR statement and I got the results I was looking for. However this brought up another issue. Is there a way to auto-resize the comment box when the different periods are selected. I looked up information and saw that the following VBA code can be run to do that
      Sub FitComments()
      'Updateby20140325
      Dim xComment As Comment
      For Each xComment In Application.ActiveSheet.Comments
      xComment.Shape.TextFrame.AutoSize = True
      Next
      End Sub

      However it is too much to ask my end-users to run the macro because each one has a different level of expertise in using excel. I need some way of doing this automatically or having the box at a preset size.

      thanks,
      • JMB17's avatar
        JMB17
        Bronze Contributor
        Mar 11, 2021

        ccivitella01 

         

        I believe you could build that into the function, if that will work for you?

         

        Public Function IndexMatchComment(arg As Variant) As Variant
     Application.Volatile True
     
     If TypeName(arg) = "Range" Then
          With Application.Caller
               If Not .Comment Is Nothing Then .Comment.Delete
               If Not arg.Comment Is Nothing Then
                    With .AddComment(arg.Comment.Text)
                         .Shape.TextFrame.AutoSize = True
                    End With
               End If
          End With
     End If
          
     IndexMatchComment = arg
     
End Function

         

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Mar 10, 2021
    I see, my version depends on the argument passed to it being a range. The index function can return a range object, but the iferror function in which it is nested does not (Iferror passes a number - double data type).

    It will work if you move Iferror:

    =IFERROR(indexmatchcomment(INDEX(Data,MATCH(1,(Line=D$2)*(Period=$B$3)*(Year=$C$3),0),17)),0)

Resources