Feb 05 2021 10:29 AM
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,
Feb 05 2021 11:18 AM - edited Feb 05 2021 12:01 PM
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.
Mar 08 2021 02:02 PM
Mar 08 2021 02:51 PM
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.
Mar 08 2021 10:51 PM
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)))
Mar 09 2021 06:01 AM
That is excellent - much simpler AND much more general! :thumbs_up:
Mar 09 2021 12:53 PM
Mar 10 2021 09:58 AM
Mar 10 2021 11:56 AM
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.
Mar 10 2021 12:06 PM
Mar 10 2021 12:31 PM - edited Mar 10 2021 12:35 PM
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).
Mar 11 2021 12:49 PM
Mar 11 2021 12:59 PM - edited Mar 11 2021 01:02 PM
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
Mar 11 2021 01:22 PM
Mar 11 2021 09:43 PM
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