Vlookup help

[ccivitella01]

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,312.78) on the Variance Tab in cell D4.

This is the formula that I have:  =IF(ISERROR(VLOOKUP(B3&C3&D2,Data,4,FALSE)),0,VLOOKUP(B3&C3&D3,Data,4,FALSE))

B3 is the period from the Variance Tab, C3 is the year from the variance tab and D2 is the Line that I want to look up.  It is a merged field that combines D2 and D3.

Data is the range that I named for the information on the Data tab.

4 is the column on the data tab that I need the information from.

The return value is 0.  What I am doing wrong in my formula?

thanks,

[#2]

@ccivitella01 

Use this, confirmed with Ctrl+Shift+Enter to turn it onto an array formula:

=IFERROR(INDEX(Data!$D$2:$D$100, MATCH(1, (Data!$A$2:$A$100=D2)*(Data!$B$2:$B$100=C3)*(Data!$C$2:$C$100=B3), 0)), "")

Adjust the ranges if needed.

[#3]

Hi Hans,

With vlookup you can vlookupcomment. Can you do the same with index match?

[#4]

@ccivitella01 

VlookupComment is not a built-in function, but a custom VBA function (see How to vlookup and return matching value with cell comment?)

You'd need a custom function here too. For example

Function IndexMatchComment(DataRange As Range, ParamArray Conditions()) As Variant
    ' Adapted from Extendoffice
    Dim r As Long
    Dim i As Long
    Dim f As Boolean
    Dim s As String
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    Application.Volatile
    With Application.Caller
        If Not .Comment Is Nothing Then
            .Comment.Delete
        End If
    End With
    For r = 1 To DataRange.Rows.Count
        f = True
        For i = 0 To UBound(Conditions) Step 2
            If Conditions(i)(r) <> Conditions(i + 1) Then
                f = False
                Exit For
            End If
        Next i
        If f Then
            Set xCell = DataRange.Cells(r, 1)
            IndexMatchComment = xCell.Value
            With Application.Caller
                If Not xCell.Comment Is Nothing Then
                    .AddComment xCell.Comment.Text
                End If
            End With
            Exit Function
        End If
    Next r
    IndexMatchComment = "N/A"
End Function

Use like this in D4 on the Variance sheet:

=IndexMatchComment(Data!$D$2:$D$5,Data!$A$2:$A$5,Sheet5!D$2,Data!$B$2:$B$5,Sheet5!$C3,Data!$C$2:$C$5,Sheet5!$B3)

Fill to the right.

 

[#5]

@Hans Vogelaar 

I think you could take advantage of Index's ability to pass a cell by reference. Perhaps shorten the UDF to:

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 .AddComment arg.Comment.Text
          End With
     End If
          
     IndexMatchComment = arg
     
End Function

Then, use your UDF as a wrapper for the usual Index/Match:

=indexmatchcomment(INDEX(Data!D2:D100,MATCH(1,(Data!A2:A100=D2)*(Data!B2:B100=C3)*(Data!C2:C100=B3),0)))

[#6]

@JMB17 

That is excellent - much simpler AND much more general!

[#7]

Thank you. That's an interesting twist on a lookup, returning cell comments along with the value.

[#8]

Thanks for the information and the simplification. i will try it.

[#9]

Thanks for the information. i will try it.

[#10]

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

[#11]

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)

[#12]

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

[#13]

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,

[#14]

@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

[#15]

@JMB17

Included new code but it did not resize. 

[#16]

@ccivitella01 

When a function is called from a worksheet, excel limits its ability to make changes to the worksheet - it appears to not allow that action to be done with a udf.

Try changing the function back to:

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 .AddComment arg.Comment.Text
          End With
     End If
          
     IndexMatchComment = arg
     
End Function

Then, in your worksheet module (Sheet10), paste this code:

Private Sub Worksheet_Calculate()
     Dim cmt As Comment
     
     For Each cmt In Me.Comments
          cmt.Shape.TextFrame.AutoSize = True
     Next cmt
     
End Sub

[#17]

@JMB17 

that did the trick.  thanks so much.