【EXCEL VBA】XLOOKUPの使い方とXLOOKUPの自作『徹底解説!!』

XLOOKUP(検索値, 検索範囲または配列, 返す範囲または配列, [見つからない場合の返り値], [比較方法], [検索モード])

=XLOOKUP(A2, B2:B10, C2:C10, "Not Found")

Function MyXLOOKUP(lookup_value As Variant, lookup_array As Range, return_array As Range, Optional if_not_found As Variant, Optional match_type As Integer = 1, Optional search_mode As Integer = 1) As Variant
    Dim i As Long
    Dim found_value As Variant

    If Not IsArray(lookup_array.Value) Or Not IsArray(return_array.Value) Then
        MyXLOOKUP = CVErr(xlErrValue)
        Exit Function
    End If

    For i = 1 To UBound(lookup_array.Value, 1)
        If match_type = 1 Then
            If lookup_array.Value(i, 1) = lookup_value Then
                found_value = return_array.Value(i, 1)
                MyXLOOKUP = IIf(IsError(found_value), if_not_found, found_value)
                Exit Function
            End If
        ElseIf match_type = -1 Then
            If lookup_array.Value(i, 1) = lookup_value Then
                found_value = return_array.Value(i, 1)
                MyXLOOKUP = IIf(IsError(found_value), if_not_found, found_value)
                Exit Function
            End If
        ElseIf match_type = 0 Then
            If lookup_array.Value(i, 1) Like lookup_value Then
                found_value = return_array.Value(i, 1)
                MyXLOOKUP = IIf(IsError(found_value), if_not_found, found_value)
                Exit Function
            End If
        End If
    Next i

    MyXLOOKUP = if_not_found
End Function

Dim result As Variant
result = MyXLOOKUP("Alice", Range("A1:A10"), Range("B1:B10"), "Not Found", 1, 1)

If Not IsError(result) Then
    MsgBox "Result: " & result
Else
    MsgBox "Error: " & result
End If

=MyXLOOKUP("Alice", A1:A10, B1:B10, "Not Found", 1, 1)

Function MyXLOOKUP(lookup_value As Variant, lookup_array As Range, return_array As Range, if_not_found As Variant, Optional match_type As Integer = 1, Optional search_mode As Integer = 1) As Variant
    ' MyXLOOKUP 関数は、指定された条件に基づいて値を検索し、結果を返します。
    ' lookup_value: 検索する値
    ' lookup_array: 検索対象の範囲
    ' return_array: 返す値がある範囲
    ' if_not_found: 検索値が見つからなかった場合のデフォルト値
    ' match_type: 検索方法 (デフォルトは 1)
    ' search_mode: 検索モード (デフォルトは 1)
    
    ' 関数の本体
    ' ...
End Function

Function myxlookup(lookup_values() As Variant, lookup_arrays() As Range, _
                   result_arrays() As Range, Optional lookup_mode As String = "exact", _
                   Optional result_mode As String = "first") As Variant

    '-----------------------------------------------------------------------
    ' MyXLOOKUP 関数
    '
    ' 概要:
    '     与えられた条件に基づいて範囲を検索し、一致する値を返す関数です。
    '
    ' 引数:
    '     lookup_value: 検索する値
    '     lookup_arrays: 検索対象の範囲
    '     result_arrays: 結果を返す範囲
    '     lookup_mode: 検索方法 ("exact"、"partial"、"criteria" のいずれか)
    '     result_mode: 結果の取得方法 ("first"、"last"、"all" のいずれか)
    '     criteria: "criteria" 検索モードで使用する条件
    '     search_range: 検索対象の範囲 (省略可能)
    '
    ' 戻り値:
    '     一致する値または結果の配列
    '
    ' 注意:
    '     MyXLOOKUP("条件", A1:A10, B1:B10) のように利用します。
    '-----------------------------------------------------------------------

    On Error GoTo ErrorHandler

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim i As Long, j As Long
    Dim found As Boolean
    Dim result() As Variant

    ' ワークシートを設定
    Set ws = lookup_array.Worksheet

    ' lookup_arrayの最終行および最終列を決定
    lastRow = lookup_array.Rows.Count
    lastCol = lookup_array.Columns.Count

    ' 検索範囲を設定
    If search_range Is Nothing Then
        Set search_range = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    End If

    ' 結果の配列を初期化
    ReDim result(1 To lastRow, 1 To lastCol)

    ' 各セルを検索範囲内でループ
    For i = 1 To lastRow
        For j = 1 To lastCol
            ' 現在のセルがlookup条件と一致するか確認
            If IsMatch(lookup_value, lookup_array.Cells(i, j).Value, lookup_mode, criteria) Then
                ' 指定されたモードに基づいて結果を記録
                If result_mode = "first" And Not found Then
                    ' 最初の一致を返す
                    result = result_array.Cells(i, j).Value
                    found = True
                ElseIf result_mode = "last" Then
                    ' 最後の一致を継続的に更新
                    result = result_array.Cells(i, j).Value
                ElseIf result_mode = "all" Then
                    ' すべての一致を2D配列に保存
                    result(i, j) = result_array.Cells(i, j).Value
                End If
            End If
        Next j
    Next i

    ' 指定されたモードに基づいて結果を返す
    If result_mode = "all" Then
        MyXLOOKUP = result
    Else
        MyXLOOKUP = Application.WorksheetFunction.IfError(result, CVErr(xlErrNA))
    End If

    Exit Function

ErrorHandler:
    ' エラーが発生した場合の処理
    MyXLOOKUP = CVErr(xlErrValue)

End Function

Function IsMatch(lookup_value As Variant, cell_value As Variant, _
                 lookup_mode As String, Optional criteria As Variant) As Boolean

    ' 現在のセルがlookup条件と一致するか確認
    On Error Resume Next

    Select Case lookup_mode
        Case "exact"
            IsMatch = (cell_value = lookup_value)
        Case "partial"
            IsMatch = (InStr(1, cell_value, lookup_value) > 0)
        Case "criteria"
            If Not IsMissing(criteria) Then
                IsMatch = Evaluate(criteria)
            End If
    End Select

    On Error GoTo 0

End Function

Dim lookup_values() As Variant
Dim lookup_arrays() As Range
Dim result_arrays() As Range
Dim result As Variant

' 検索値
lookup_values = Array("Alice", "30")

' 検索対象の範囲
Set lookup_arrays(0) = Range("A1:A10")
Set lookup_arrays(1) = Range("C1:C10")

' 結果の範囲
Set result_arrays(0) = Range("B1:B10")
Set result_arrays(1) = Range("D1:D10")

' 複数条件での検索
result = myxlookup(lookup_values, lookup_arrays, result_arrays, "exact", "first")

=myxlookup(A1:A10, B1:B10, C1:C10, "exact", "first")

=myxlookup({"Alice", "30"}, {A1:A10, C1:C10}, {B1:B10, D1:D10}, "exact", "first")

Function MyXLOOKUP( _
    ByRef lookup_value As Variant, _
    ByRef lookup_array As Variant, _
    ByRef return_array As Variant, _
    Optional match_type As String = "exact", _
    Optional search_mode As String = "first") As Variant

    ' 関数の引数を処理

    ' ここに検索処理を実装

    ' 結果を出力
    MyXLOOKUP = result ' result は関数が返す結果の配列
End Function

#include <windows.h>

extern "C" __declspec(dllexport) double __stdcall MyXLOOKUP(double lookup_value, double* lookup_array, double* return_array, double if_not_found, int match_type, int search_mode, int array_length) {
    for (int i = 0; i < array_length; ++i) {
        if (lookup_array[i] == lookup_value) {
            return return_array[i];
        }
    }

    return if_not_found;
}

cl /LD myxlookup.cpp

Declare Function MyXLOOKUP Lib "path\to\myxlookup.dll" Alias "MyXLOOKUP" (ByVal lookup_value As Double, lookup_array As Double, return_array As Double, ByVal if_not_found As Double, ByVal match_type As Long, ByVal search_mode As Long, ByVal array_length As Long) As Double

=MyXLOOKUP(A1, B1:B10, C1:C10, -1, 1, 1, COUNT(B1:B10))

#include <windows.h>
#include <string>
#include <vector>

template <typename T>
extern "C" __declspec(dllexport) T __stdcall MyXLOOKUP(T lookup_value, T* lookup_array, T* return_array, T if_not_found, int match_type, int search_mode, int array_length) {
    // 文字列の場合は std::wstring を使用
    if constexpr (std::is_same<T, std::wstring>::value) {
        for (int i = 0; i < array_length; ++i) {
            if (lookup_array[i] == lookup_value) {
                return return_array[i];
            }
        }
    } else {
        // それ以外の型は通常の比較
        for (int i = 0; i < array_length; ++i) {
            if (lookup_array[i] == lookup_value) {
                return return_array[i];
            }
        }
    }

    return if_not_found;
}

// 明示的なインスタンス化
template double __stdcall MyXLOOKUP<double>(double, double*, double*, double, int, int, int);
template int __stdcall MyXLOOKUP<int>(int, int*, int*, int, int, int, int);
template std::wstring __stdcall MyXLOOKUP<std::wstring>(std::wstring, std::wstring*, std::wstring*, std::wstring, int, int, int);