I'm trying to write a custom XLOOKUP function that will allow me to specify adjustments to the original lookup value if a match isn't found

huangapple
go评论168阅读模式
英文:

I'm trying to write a custom XLOOKUP function that will allow me to specify adjustments to the original lookup value if a match isn't found

问题

I hate having nested lookups as deep as mine are so I thought it would be a simple task to add an optional modifier parameter that, if utilized, would tell the function to adjust the lookup value and try to look again. For example, =VariableXLOOKUP(A1,B;B,C:C,-0.01) would look for A1 like normal but if no match is found, it would look for A1-0.01. I feel like I have tried every way I can think of to write this and while I can replicate the normal xlookup function, I cannot get it to show results that require the use of the modifier. Ideally, I'd like to be able to list as many modifiers in the function as I want but I am fine capping it at less than 10 as well.

The first version I tried that would not show results that required the modifier:

  1. Function VariableXLOOKUP(lookup_value As Variant, lookup_range As Range, return_range As Range, modifier As Variant) As Variant
  2.     Dim result As Variant
  4.     ' Try the initial lookup
  5.     result = Application.WorksheetFunction.XLookup(lookup_value, lookup_range, return_range)
  7.     ' If no match and modifier is specified, try again with modified criteria
  8.     If IsError(result) Then
  9.         result = Application.WorksheetFunction.XLookup(lookup_value + modifier, lookup_range, return_range)
  10.     End If
  12.     VariableXLOOKUP = result
  13. End Function

Then I tried this version with the same results:

  1. Function VariableXLOOKUP(lookup_value As Variant, lookup_range As Range, return_range As Range, ParamArray modifiers() As Variant) As Variant
  2.     Dim result As Variant
  3.     Dim i As Long
  5.     ' Try the initial lookup
  6.     result = Application.WorksheetFunction.XLookup(lookup_value, lookup_range, return_range)
  8.     ' If no match and modifiers are specified, try again with each modifier
  9.     If IsError(result) And Not IsMissing(modifiers) Then
  10.         For i = LBound(modifiers) To UBound(modifiers)
  11.             result = Application.WorksheetFunction.XLookup(lookup_value + modifiers(i), lookup_range, return_range)
  12.             If Not IsError(result) Then Exit For ' Exit loop if a match is found
  13.         Next i
  14.     End If
  16.     VariableXLOOKUP = result
  17. End Function

I had also tried a version that used Index and Match, but in all iterations of this or the xlookup version, it seems to never return results from the loop part of the function. I have modified the code so the initial lookup is result = Application.WorksheetFunction.XLookup(lookup_value + modifier, lookup_range, return_range) and it does return the results I expect (matches for lookup_value + modifier) so the issue is with error handling and moving on to the second lookup attempt as all lookup variations that I've typed work if they are the initial lookup in the function but fail as soon as a second look is required.

**Edited for grammar and spelling.

英文:

I hate having nested lookups as deep as mine are so I thought it would be a simple task to add an optional modifier parameter that, if utilized, would tell the function to adjust the lookup value and try to look again. For example =VariableXLOOKUP(A1,B;B,C:C,-0.01) ould look for A1 like normal but if no match is found, it would look for A1-0.01. I feel like I have tried every way I can think of to write this and while I can replicate the normal xlookup function, I cannot get it to show results that require the use of the modifier. Ideally, I'd like to be able to list as many modifiers in the function as I want but I am fine capping it at less than 10 as well.

The first version I tried that would not show results that required the modifier:

  1. Function VariableXLOOKUP(lookup_value As Variant, lookup_range As Range, return_range As Range, modifier As Variant) As Variant
  2.     Dim result As Variant
  4.     ' Try the initial lookup
  5.     result = Application.WorksheetFunction.XLookup(lookup_value, lookup_range, return_range)
  7.     ' If no match and modifier is specified, try again with modified criteria
  8.     If IsError(result) Then
  9.         result = Application.WorksheetFunction.XLookup(lookup_value + modifier, lookup_range, return_range)
  10.     End If
  12.     VariableXLOOKUP = result
  13. End Function

Then I tried this version with the same results:

  1. Function VariableXLOOKUP(lookup_value As Variant, lookup_range As Range, return_range As Range, ParamArray modifiers() As Variant) As Variant
  2.     Dim result As Variant
  3.     Dim i As Long
  5.     ' Try the initial lookup
  6.     result = Application.WorksheetFunction.XLookup(lookup_value, lookup_range, return_range)
  8.     ' If no match and modifiers are specified, try again with each modifier
  9.     If IsError(result) And Not IsMissing(modifiers) Then
  10.         For i = LBound(modifiers) To UBound(modifiers)
  11.             result = Application.WorksheetFunction.XLookup(lookup_value + modifiers(i), lookup_range, return_range)
  12.             If Not IsError(result) Then Exit For ' Exit loop if a match is found
  13.         Next i
  14.     End If
  16.     VariableXLOOKUP = result
  17. End Function

I had also tried a version that used Index and Match but in all iterations of this or the xlookup version, it seems to never return results from the loop part of the function.
I have modified the code so the initial lookup is result = Application.WorksheetFunction.XLookup(lookup_value + modifier, lookup_range, return_range) and it does return the results I expect (matches for lookup_value + modifier) so the issue is with error handling and moving on to the second lookup attempt as all lookup variations that I've typed work if they are the initial lookup in the function but fail as soon as a second look is required.

**Edited for grammar and spelling

答案1

得分: 3

这段代码的翻译如下：

这对我有用：

  1. Sub tester()
  2.     Debug.Print VariableXLOOKUP(6, [A1:A10], [B1:B10])
  3.     Debug.Print VariableXLOOKUP(6, [A1:A10], [B1:B10], -0.1)
  4.     Debug.Print VariableXLOOKUP(6, [A1:A10], [B1:B10], -0.1, 0.1, 0.2)
  5. End Sub
  7. Function VariableXLOOKUP(lookup_value As Variant, lookup_range As Range, _
  8.                          return_range As Range, ParamArray modifiers() As Variant) As Variant
  9.     Dim result As Variant, x As Long, modVal
  11.     result = Application.XLookup(lookup_value, lookup_range, return_range)
  13.     If IsError(result) Then
  14.         For x = LBound(modifiers) To UBound(modifiers)
  15.             modVal = modifiers(x)
  16.             Debug.Print "Using modifier: " & modVal
  17.             result = Application.XLookup(lookup_value + modVal, _
  18.                                                 lookup_range, return_range)
  19.             If Not IsError(result) Then Exit For 'got a match...
  20.         Next x
  21.     End If
  22.     VariableXLOOKUP = IIf(IsError(result), "No match", result)
  23. End Function

去掉 WorksheetFunction 允许查找返回一个错误值。

使用 ParamArray 允许您传递一个或多个修饰符，以尝试如果未找到原始值。 参见：https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-parameter-arrays

英文:

This works for me:

  1. Sub tester()
  2.     Debug.Print VariableXLOOKUP(6, [A1:A10], [B1:B10])
  3.     Debug.Print VariableXLOOKUP(6, [A1:A10], [B1:B10], -0.1)
  4.     Debug.Print VariableXLOOKUP(6, [A1:A10], [B1:B10], -0.1, 0.1, 0.2)
  5. End Sub
  7. Function VariableXLOOKUP(lookup_value As Variant, lookup_range As Range, _
  8.                          return_range As Range, ParamArray modifiers() As Variant) As Variant
  9.     Dim result As Variant, x As Long, modVal
  11.     result = Application.XLookup(lookup_value, lookup_range, return_range)
  13.     If IsError(result) Then
  14.         For x = LBound(modifiers) To UBound(modifiers)
  15.             modVal = modifiers(x)
  16.             Debug.Print "Using modifier: " & modVal
  17.             result = Application.XLookup(lookup_value + modVal, _
  18.                                                 lookup_range, return_range)
  19.             If Not IsError(result) Then Exit For 'got a match...
  20.         Next x
  21.     End If
  22.     VariableXLOOKUP = IIf(IsError(result), "No match", result)
  23. End Function

Dropping the WorksheetFunction allows the lookup to return an error value.

Using a paramarray allows you to pass in one or more modifiers to try if the original value isn't found. See: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-parameter-arrays

huangapple
  • 本文由 发表于 2023年8月11日 01:50:27
  • 转载请务必保留本文链接：https://go.coder-hub.com/76878200.html
  • excel
  • function
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定