Supported Excel Functions

Logical

Lookup Functions

Math and Statistical

Rounding

Text

XLOOKUP

LAMBDA

XLOOKUP Function

The Adaptive Calculation Engine ACE4J XLOOKUP function's behavior and syntax are almost identical to the Excel XLOOKUP function. Some insignificant differences are related to their platform differences (Java vs. Excel), while others constitute advantages of the ACE4J XLOOKUP function such as its ability to perform a suitable binary search even if lookup_array elements are not sorted.

ACE4J XLOOKUP Implementation

Syntax

XLOOKUP(lookup_value, lookup_array_name, return_array_name, [if_not_found], [match_mode], [seach_mode])

lookup_value Required.
The lookup value.

lookup_array_name Required.
The name of a single row or single column TableArray object in which the XLOOKUP will search for the lookup_value. TableArray objects are placed in the HashMap and retrieved by their names during the formula evaluation.

return_array_name Required.
The name of a TableArray object from which the XLOOKUP will retrieve the return value.

[if_not_found] Optional.
Where a valid match is not found, the formula field getDisplayValue() method returns the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A will be returned.

[match_mode] Optional.
Specify the match type:

0Exact match. If none found, return #N/A. This is the default.
-1 Exact match. If none found, return the next smaller item.
1 Exact match. If none found, return the next larger item.
2 A wildcard match.

If the match_mode is 2 and the lookup_value is a text string, you can use the wildcard characters − the question mark (?) and asterisk (*) in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

[seach_mode] Optional. Specify the search mode to use:

1 Perform a search starting at the first item. This is the default.
-1 Perform a search starting at the last item.
2 Perform a binary search even if the lookup_array is not sorted.

The lookup_array elements are not required to be sorted for any seach_mode value.

The XLOOKUP function searches a lookup_array and returns an item from a return_array, which corresponds to the first match it finds. If a match does not exist, XLOOKUP can return the closest (approximate) match. If XLOOKUP returns an array of values for the entire column or row, it must be used as an argument of one of the following functions: XLOOKUP, MAX, MIN, SUM, AVERAGE, MEDIAN, MODE, COUNT, COUNTA, COUNTBLANK, CONCATENATE, and CONCAT or an error will be produced. This is the Adaptive Calculation Engine implementation of Excel XLOOKUP function. In this XLOOKUP implementation the optional search_mode parameter has a slightly different set of allowed values than the original one. A TableArray object used as the lookup_array is automatically sorted in the applicable order and a binary search is applied whenever possible.

The below examples were constructed based on the XLOOKUP examples published on the Microsoft Office Support site.

Example 1

The first example uses a basic 3-parameter version of XLOOKUP to look up a car make, then returns its model. It includes lookup_value ("VOLVO"), lookup_array (range C5:C12), and return_array (range D5:D12) arguments. The basic 3-parameter XLOOKUP defaults to an exact match:

Example of the XLOOKUP function returning a car model based on car make. The formula is =XLOOKUP (B2,C5:C12,D5:D12).

Both TableArray objects are then placed in the tableArrays HashMap with keys named "car_make" and "model." The below ACE4J XLOOKUP formula my_formula is equivalent to the XLOOKUP (B2,C5:C12,D5:D12) formula in this example:

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class XlookupExample1 {
    private static TableArray getTableArray(Object[][] lookupTable, int startRowIndex,
                                            int startColumnIndex, int endRowIndex,
                                            int endColumnIndex) {
        return (new TableArray(lookupTable, startRowIndex, startColumnIndex,
                    endRowIndex, endColumnIndex));
    }

    public static void main(String[] args) {
        Object[][] lookupTable = {{"Country",       "Car Make",       "Model"},
                                  {"France",        "PEUGEOT",         "1007"},
                                  {"Germany",       "BMW",               "X3"},
                                  {"Italy",         "ALFA ROMEO",    "GIULIA"},
                                  {"Japan",         "TOYOTA",         "CAMRY"},
                                  {"South Korea",   "KIA",           "SELTOS"},
                                  {"Sweden",        "VOLVO",            "V90"},
                                  {"UK",            "LAND ROVER",  "DEFENDER"},
                                  {"United States", "FORD",          "RANGER"}};

        // Car Make column without its title
        TableArray carMake = getTableArray(lookupTable, 1, 1,
                lookupTable.length - 1, 1);
        // Model column without its title
        TableArray model = getTableArray(lookupTable, 1, lookupTable[0].length - 1,
                lookupTable.length - 1, lookupTable[0].length - 1);

        CalculationContext calculationContext = new CalculationContext("XlookupExample1");
        InputField car = new InputField(ComputeFieldType.ALPHANUMERIC, "VOLVO");
        String myFormulaText = "XLOOKUP(car, \"Car_Make\", \"Model\")";
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.ALPHANUMERIC, myFormulaText);

        // ComputeFields
        calculationContext.put("car", car);
        calculationContext.put("model_formula", myFormulaField);
        // TableArrays
        calculationContext.put("Car_Make", carMake);
        calculationContext.put("Model", model);

        HybridFormulaEvaluator.evaluateFormula("model_formula", calculationContext);
        System.out.printf("Car make: %s \nModel: %s", car.getDisplayValue(),
                myFormulaField.getDisplayValue());
    }
}
            
Car make: VOLVO
Model: V90

Example 2

The second example is looking up movie information based on its budget. Unlike VLOOKUP, XLOOKUP is able to return an array with multiple items, which allows a single formula to return both genre and title from cells B5:C12:

Example of the XLOOKUP function returning genre and title based on movie budget The formula is =XLOOKUP( B2,D5:D12,C5:C12,'Title not found').

The Adaptive Calculation Engine version of this example uses the two-dimensional array lookupTable corresponding to the above shown range of cells B4:D12. (lookupTable uses one additional column with spaces for demonstration purposes). Using this lookupTable it creates the TableArray object budget corresponding to the range of cells D5:D12, and the TableArray object genreTitle corresponding to the range of cells B5:C12. Both TableArray objects are then placed in the tableArrays HashMap with keys named "budget" and "genre_and_title." The ACE4J XLOOKUP function in the below formula my_formula is equivalent to the XLOOKUP( B2,D5:D12,B5:C12) formula in this example. The movie genre and title returned by ACE4J XLOOKUP will be consumed by the CONCATENATE function added for demonstration purposes:

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class XlookupExample2 {
    private static TableArray getTableArray(Object[][] lookupTable, int startRowIndex,
                                            int startColumnIndex, int endRowIndex,
                                            int endColumnIndex) {
        return (new TableArray(lookupTable, startRowIndex, startColumnIndex,
                endRowIndex, endColumnIndex));
    }

    public static void main(String[] args) {
        Object[][] lookupTable = {{"Genre",     " ", "Title",                    "Budget"},
                                  {"Comedy",    " ", "My Spy",                18000000.00},
                                  {"Grime",     " ", "The host",              40000000.00},
                                  {"Drama",     " ", "Little Women",          40000000.00},
                                  {"Sci-Fi",    " ", "Ad Astra",              80000000.00},
                                  {"Adventure", " ", "The Call of the Wild", 109000000.00},
                                  {"Animation", " ", "Missing Link",         150000000.00},
                                  {"Action",    " ", "No Time to Die",       250000000.00},
                                  {"Family",    " ", "Lion King",            260000000.00}};

        // Budget column without its title
        TableArray budget = getTableArray(lookupTable, 1, lookupTable[0].length - 1,
                lookupTable.length - 1, lookupTable[0].length - 1);
        // Genre and Title columns without their titles and empty column in between
        TableArray genreTitle = getTableArray(lookupTable, 1, 0,
                lookupTable.length - 1, 2);
        CalculationContext calculationContext = new CalculationContext("XlookupExample2");
        InputField movieBudget = new InputField(ComputeFieldType.NUMERIC, 80000000.00);
        String myFormulaText = "CONCATENATE(XLOOKUP(movie_budget, \"Budget\", \"Genre_and_Title\"))";
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.ALPHANUMERIC, myFormulaText);;

        // ComputeFields
        calculationContext.put("movie_budget", movieBudget);
        calculationContext.put("my_formula", myFormulaField);
        // TableArrays
        calculationContext.put("Budget", budget);
        calculationContext.put("Genre_and_Title", genreTitle);

        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println(myFormulaField.getDisplayValue());
    }
}

Sci-Fi Ad Astra

Example 3

The third example adds the if_not_found parameter to the formula in the previous example:

Example of the XLOOKUP function using if_not_found parameter. The formula is =XLOOKUP (B2,C5:C12,D5:D12).

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class XlookupExample3 {
    private static TableArray getTableArray(Object[][] lookupTable, int startRowIndex,
                                            int startColumnIndex, int endRowIndex,
                                            int endColumnIndex) {
        return (new TableArray(lookupTable, startRowIndex, startColumnIndex,
                endRowIndex, endColumnIndex));
    }

    public static void main(String[] args) {
        Object[][] lookupTable = {{"Genre",     " ", "Title",                    "Budget"},
                                  {"Comedy",    " ", "My Spy",                18000000.00},
                                  {"Grime",     " ", "The host",              40000000.00},
                                  {"Drama",     " ", "Little Women",          40000000.00},
                                  {"Sci-Fi",    " ", "Ad Astra",              80000000.00},
                                  {"Adventure", " ", "The Call of the Wild", 109000000.00},
                                  {"Animation", " ", "Missing Link",         150000000.00},
                                  {"Action",    " ", "No Time to Die",       250000000.00},
                                  {"Family",    " ", "Lion King",            260000000.00}};

        // Budget column without its title
        TableArray budget = getTableArray(lookupTable, 1, lookupTable[0].length - 1,
                lookupTable.length - 1, lookupTable[0].length - 1);
        // Genre and Title columns without their titles and empty column in between
        TableArray genreTitle = getTableArray(lookupTable, 1, 0,
                lookupTable.length - 1, 2);

        CalculationContext calculationContext = new CalculationContext("XlookupExample3");
        InputField movieBudget = new InputField(ComputeFieldType.NUMERIC, 90000000.00);
        String myFormulaText = "CONCATENATE(XLOOKUP(movie_budget, \"budget\", \"genre_and_title\"," +
                " \"movie not found\"))";
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.ALPHANUMERIC, myFormulaText);

        // ComputeFields
        calculationContext.put("movie_budget", movieBudget);
        calculationContext.put("my_formula", myFormulaField);
        // TableArrays
        calculationContext.put("Budget", budget);
        calculationContext.put("Genre_and_Title", genreTitle);

        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println("90 million budget " + myFormulaField.getDisplayValue());
    }
}

90 million budget movie not found

Example 4

The fourth example looks in column D for the budget entered in cell B2, and finds a matching title in column C. It sets the if_not_found argument to return a "title not found" text if nothing is found. The match_mode argument is set to 1, which means the function will look for an exact match, and if it can't find one, it will return the next larger item. Finally, the search_mode argument is set to 1, which means the function will search from the first item to the last.

Example of the XLOOKUP function used to return a movie performing an approximate match. The formula is =XLOOKUP( B2,D5:D12,C5:C12,'Title not found' ,1,1).

The Adaptive Calculation Engine version of this example uses the two-dimensional array lookupTable corresponding to the above shown range of cells B4:D12. Using this lookupTable it creates the TableArray object budget corresponding to the range of cells D5:D12, and the TableArray object title corresponding to the range of cells C5:C12. Both TableArray objects are then placed in the tableArrays HashMap with keys named "budget" and "title." The if_not_found argument is set to "Title not found" and match_mode argument is set to 1, exactly as it is done in the Excel XLOOKUP formula of this example. The search_mode argument is also set to 1. This combination of match_mode and search_mode arguments allows the below XLOOKUP expression to successfully perform a suitable binary search. The ACE4J XLOOKUP is capable of performing such a search even if the "budget" column is not sorted. The below ACE4J XLOOKUP formula is equivalent to the XLOOKUP( B2,D5:D12,C5:C12, "Title not found",1,1) formula in this example:

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class XlookupExample4 {
    private static TableArray getTableArray(Object[][] lookupTable, int startRowIndex,
                                            int startColumnIndex, int endRowIndex,
                                            int endColumnIndex) {
        return (new TableArray(lookupTable, startRowIndex, startColumnIndex,
                endRowIndex, endColumnIndex));
    }

    public static void main(String[] args) {
        Object[][] lookupTable = {{"Genre",     "Title",                    "Budget"},
                                  {"Comedy",    "My Spy",                18000000.00},
                                  {"Grime",     "The host",              40000000.00},
                                  {"Drama",     "Little Women",          40000000.00},
                                  {"Sci-Fi",    "Ad Astra",              80000000.00},
                                  {"Adventure", "The Call of the Wild", 109000000.00},
                                  {"Animation", "Missing Link",         150000000.00},
                                  {"Action",    "No Time to Die",       250000000.00},
                                  {"Family",    "Lion King",            260000000.00}};

        // Budget column without its title
        TableArray budget = getTableArray(lookupTable, 1, lookupTable[0].length - 1,
                lookupTable.length - 1, lookupTable[0].length - 1);
        // Title column without its title
        TableArray title = getTableArray(lookupTable, 1, 1,
                lookupTable.length - 1, 1);

        CalculationContext calculationContext = new CalculationContext("XlookupExample4");
        InputField movieBudget = new InputField(ComputeFieldType.NUMERIC, 130000000.00);
        String myFormulaText = "XLOOKUP(movie_budget, \"Budget\", \"Title\", \"title not found\", 1, 1)";
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.ALPHANUMERIC, myFormulaText);;

        // ComputeFields
        calculationContext.put("movie_budget", movieBudget);
        calculationContext.put("my_formula", myFormulaField);
        // TableArrays
        calculationContext.put("Budget", budget);
        calculationContext.put("Title", title);

        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println(myFormulaField.getDisplayValue());
    }
}
    
Missing Link

Example 5

The fifth example uses a nested XLOOKUP function to perform both a vertical and horizontal match. In this case, it will first look for Wimbledon Grand Slam tournament in row 4, then look for the player named Pete Sampras in column B, and return the value at the intersection of the two. This is similar to using the INDEX and MATCH functions in conjunction.

Example of the nested XLOOKUP function performing a vertical and horizontal match. The formula is =XLOOKUP(C2,B5:B11,XLOOKUP(B2,C4:F4,C5:F11)).

The Adaptive Calculation Engine version of this example uses the two-dimensional array lookupTable corresponding to the above shown range of cells B4:G11. Using this lookupTable it creates the TableArray object tournament corresponding to the range of cells C4:F4, the TableArray object numberOfTimes corresponding to the range of cells C5:F11, and the TableArray object tennisPlayer corresponding to the range of cells B5:B11. These TableArray objects are placed in the tableArrays HashMap with keys named "tournament," "number_of_timest," and "tennis_player." The inner XLOOKUP function returns the Wimbledon column, which will be used as the return_array of the outer XLOOKUP function.

The below ACE4J XLOOKUP formula is equivalent to the XLOOKUP (C2,B5:B11,XLOOKUP(B2,C4:F4,C5:F11)) formula in this example:

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class XlookupExample5 {
    private static TableArray getTableArray(Object[][] lookupTable, int startRowIndex,
                                            int startColumnIndex, int endRowIndex,
                                            int endColumnIndex) {
        return (new TableArray(lookupTable, startRowIndex, startColumnIndex,
                endRowIndex, endColumnIndex));
    }

    public static void main(String[] args) {
        Object[][] lookupTable =
                    {{"Tennis Player", "Australian Open", "Rolan Garros", "Wimbledon",  "US Open", "Total"},
                     {"Roger Federer",                 6,              1,           8,          5,      20},
                     {"Rafael Nadal",                  1,             12,           2,          4,      19},
                     {"Novak Djokovic",                8,              1,           5,          3,      17},
                     {"Pete Sampras",                  2,              0,           7,          5,      14},
                     {"Roy Emerson",                   6,              2,           2,          2,      12},
                     {"Bjorn Borg",                    0,              6,           5,          0,      11},
                     {"Rodney Laver",                  3,              2,           4,          2,      11}};
        // Tournaments : Australian Open, Rolan Garros, Wimbledon, US Open
        TableArray tournaments = getTableArray(lookupTable, 0, 1, 0,4);
        // Number of times each player won Grand Slam tournaments
        TableArray numberOfTimes = getTableArray(lookupTable, 1, 1, lookupTable.length - 1,4);
        // Tennis Player column without its title
        TableArray tennisPlayer = getTableArray(lookupTable, 1, 0, lookupTable.length - 1,0);

        CalculationContext calculationContext = new CalculationContext("XlookupExample5");
        InputField selectedGrandSlam = new InputField(ComputeFieldType.ALPHANUMERIC, "Wimbledon");
        InputField selectedPlayer = new InputField(ComputeFieldType.ALPHANUMERIC, "Pete Sampras");
        String myFormulaText = "XLOOKUP(selected_player, \"Tennis_Player\", XLOOKUP(selected_grand_slam, " +
                "\"Tournaments\", \"Number_of_Times\"))";
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.NUMERIC, myFormulaText);;

        // ComputeFields
        calculationContext.put("selected_grand_slam", selectedGrandSlam);
        calculationContext.put("selected_player", selectedPlayer);
        calculationContext.put("my_formula", myFormulaField);
        // TableArrays
        calculationContext.put("Tournaments", tournaments);
        calculationContext.put("Number_of_Times", numberOfTimes);
        calculationContext.put("Tennis_Player", tennisPlayer);

        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println("Pete Sampras won Wimbledon " + myFormulaField.getDisplayValue() + " times");
    }
}

Pete Sampras won Wimbledon 7 times