Vlookup help

%3CLINGO-SUB%20id%3D%22lingo-sub-2115796%22%20slang%3D%22en-US%22%3EVlookup%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2115796%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20where%20I%20need%20to%20lookup%20a%20value%20based%20on%20a%20drop%20down%20menu%20that%20changes%20for%20the%20period%20and%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVariance%20Tab%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ccivitella01_0-1612549118349.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F252765iF25A393628F6ED24%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ccivitella01_0-1612549118349.png%22%20alt%3D%22ccivitella01_0-1612549118349.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EData%20Tab%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ccivitella01_1-1612549255853.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F252766i0E7B008193AC44CF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ccivitella01_1-1612549255853.png%22%20alt%3D%22ccivitella01_1-1612549255853.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20a%20return%20value%20of%20(%2423%2C312.78)%20on%20the%20Variance%20Tab%20in%20cell%20D4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20that%20I%20have%3A%26nbsp%3B%20%3DIF(ISERROR(VLOOKUP(B3%26amp%3BC3%26amp%3BD2%2CData%2C4%2CFALSE))%2C0%2CVLOOKUP(B3%26amp%3BC3%26amp%3BD3%2CData%2C4%2CFALSE))%3C%2FP%3E%3CP%3EB3%20is%20the%20period%20from%20the%20Variance%20Tab%2C%20C3%20is%20the%20year%20from%20the%20variance%20tab%20and%20D2%20is%20the%20Line%20that%20I%20want%20to%20look%20up.%26nbsp%3B%20It%20is%20a%20merged%20field%20that%20combines%20D2%20and%20D3.%3C%2FP%3E%3CP%3EData%20is%20the%20range%20that%20I%20named%20for%20the%20information%20on%20the%20Data%20tab.%3C%2FP%3E%3CP%3E4%20is%20the%20column%20on%20the%20data%20tab%20that%20I%20need%20the%20information%20from.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20return%20value%20is%200.%26nbsp%3B%20What%20I%20am%20doing%20wrong%20in%20my%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2115796%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2115842%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2115842%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F957040%22%20target%3D%22_blank%22%3E%40ccivitella01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20this%2C%20confirmed%20with%20Ctrl%2BShift%2BEnter%20to%20turn%20it%20onto%20an%20array%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(Data!%24D%242%3A%24D%24100%2C%20MATCH(1%2C%20(Data!%24A%242%3A%24A%24100%3DD2)*(Data!%24B%242%3A%24B%24100%3DC3)*(Data!%24C%242%3A%24C%24100%3DB3)%2C%200))%2C%20%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdjust%20the%20ranges%20if%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2194492%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2194492%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20vlookup%20you%20can%20vlookupcomment.%20Can%20you%20do%20the%20same%20with%20index%20match%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2194563%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2194563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F957040%22%20target%3D%22_blank%22%3E%40ccivitella01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVlookupComment%20is%20not%20a%20built-in%20function%2C%20but%20a%20custom%20VBA%20function%20(see%20%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4561-excel-vlookup-cell-comments.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20to%20vlookup%20and%20return%20matching%20value%20with%20cell%20comment%3F%3C%2FA%3E)%3C%2FP%3E%0A%3CP%3EYou'd%20need%20a%20custom%20function%20here%20too.%20For%20example%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20IndexMatchComment(DataRange%20As%20Range%2C%20ParamArray%20Conditions())%20As%20Variant%0A%20%20%20%20'%20Adapted%20from%20Extendoffice%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20f%20As%20Boolean%0A%20%20%20%20Dim%20s%20As%20String%0A%20%20%20%20Dim%20xRet%20As%20Variant%20'could%20be%20an%20error%0A%20%20%20%20Dim%20xCell%20As%20Range%0A%20%20%20%20Application.Volatile%0A%20%20%20%20With%20Application.Caller%0A%20%20%20%20%20%20%20%20If%20Not%20.Comment%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20.Comment.Delete%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20With%0A%20%20%20%20For%20r%20%3D%201%20To%20DataRange.Rows.Count%0A%20%20%20%20%20%20%20%20f%20%3D%20True%0A%20%20%20%20%20%20%20%20For%20i%20%3D%200%20To%20UBound(Conditions)%20Step%202%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Conditions(i)(r)%20%26lt%3B%26gt%3B%20Conditions(i%20%2B%201)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20f%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Exit%20For%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20i%0A%20%20%20%20%20%20%20%20If%20f%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Set%20xCell%20%3D%20DataRange.Cells(r%2C%201)%0A%20%20%20%20%20%20%20%20%20%20%20%20IndexMatchComment%20%3D%20xCell.Value%0A%20%20%20%20%20%20%20%20%20%20%20%20With%20Application.Caller%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20xCell.Comment%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.AddComment%20xCell.Comment.Text%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20%20%20%20%20%20%20%20%20Exit%20Function%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20r%0A%20%20%20%20IndexMatchComment%20%3D%20%22N%2FA%22%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUse%20like%20this%20in%20D4%20on%20the%20Variance%20sheet%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIndexMatchComment(Data!%24D%242%3A%24D%245%2CData!%24A%242%3A%24A%245%2CSheet5!D%242%2CData!%24B%242%3A%24B%245%2CSheet5!%24C3%2CData!%24C%242%3A%24C%245%2CSheet5!%24B3)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0187.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F261828i53AA7F9F98A58800%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0187.png%22%20alt%3D%22S0187.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2195171%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2195171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20could%20take%20advantage%20of%20Index's%20ability%20to%20pass%20a%20cell%20by%20reference.%20Perhaps%20shorten%20the%20UDF%20to%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPublic%20Function%20IndexMatchComment(arg%20As%20Variant)%20As%20Variant%0A%20%20%20%20%20Application.Volatile%20True%0A%20%20%20%20%20%0A%20%20%20%20%20If%20TypeName(arg)%20%3D%20%22Range%22%20Then%0A%20%20%20%20%20%20%20%20%20%20With%20Application.Caller%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20.Comment%20Is%20Nothing%20Then%20.Comment.Delete%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20arg.Comment%20Is%20Nothing%20Then%20.AddComment%20arg.Comment.Text%0A%20%20%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20IndexMatchComment%20%3D%20arg%0A%20%20%20%20%20%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20use%20your%20UDF%20as%20a%20wrapper%20for%20the%20usual%20Index%2FMatch%3A%3C%2FP%3E%3CP%3E%3Dindexmatchcomment(INDEX(Data!D2%3AD100%2CMATCH(1%2C(Data!A2%3AA100%3DD2)*(Data!B2%3AB100%3DC3)*(Data!C2%3AC100%3DB3)%2C0)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2195996%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2195996%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20excellent%20-%20much%20simpler%20AND%20much%20more%20general!%20%3Athumbs_up%3A%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

ccivitella01_0-1612549118349.png

Data Tab

ccivitella01_1-1612549255853.png

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,

16 Replies

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

Hi Hans,

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

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

 

S0187.png

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

@JMB17 

That is excellent - much simpler AND much more general!

Thank you. That's an interesting twist on a lookup, returning cell comments along with the value.
Thanks for the information and the simplification. i will try it.
Thanks for the information. i will try it.

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

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)

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

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,

@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

 

Included new code but it did not resize. 

@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

@JMB17 

 

that did the trick.  thanks so much.