Bug 130359 - XLOOKUP() function
Summary: XLOOKUP() function
Status: RESOLVED DUPLICATE of bug 127293
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Reported: 2020-02-02 06:47 UTC by Óvári
Modified: 2020-02-02 08:26 UTC (History)
0 users

See Also:
Crash report or crash signature:

Example 1 (101.74 KB, image/png)
2020-02-02 06:49 UTC, Óvári
Example 2 (158.68 KB, image/png)
2020-02-02 06:50 UTC, Óvári
Example 3 (157.84 KB, image/png)
2020-02-02 06:51 UTC, Óvári
Example 4 (76.13 KB, image/png)
2020-02-02 06:52 UTC, Óvári
Example 5 (182.30 KB, image/png)
2020-02-02 06:53 UTC, Óvári
Example 6 (85.36 KB, image/png)
2020-02-02 06:54 UTC, Óvári

Note You need to log in before you can comment on or make changes to this bug.
Description Óvári 2020-02-02 06:47:04 UTC
Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.


Steps to Reproduce:
New XLOOKUP() function for LibreOffice Calc

Actual Results:
Unknown function

Expected Results:
XLOOKUP() function known and variations working

Reproducible: Always

User Profile Reset: No

Additional Info:
Examples to follow
Comment 1 Óvári 2020-02-02 06:49:28 UTC
Created attachment 157590 [details]
Example 1

Example 1

This example uses a simple XLOOKUP to look up a country name, then return its telephone country code. It only includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It does not include the match_mode argument, as XLOOKUP defaults to an exact match.

Note: XLOOKUP is different from VLOOKUP in that it uses separate lookup and return arrays, where VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula in this case would be:
Comment 2 Óvári 2020-02-02 06:50:43 UTC
Created attachment 157591 [details]
Example 2

Example 2

In this example, we're looking up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP is able to return an array with multiple items, which allows a single formula to return both employee name and department from cells C5:D14.
Comment 3 Óvári 2020-02-02 06:51:26 UTC
Created attachment 157592 [details]
Example 3

Example 3

This example adds the if_not_found argument to the example above.
Comment 4 Óvári 2020-02-02 06:52:31 UTC
Created attachment 157593 [details]
Example 4

Example 4

The following example looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It sets the if-not_found argument to return a 0 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.

Note: Unlike VLOOKUP, the lookup_array column is to the right of the return_array column, where VLOOKUP can only look from left-to-right.
Comment 5 Óvári 2020-02-02 06:53:40 UTC
Created attachment 157594 [details]
Example 5

Example 5

Next, we'll use a nested XLOOKUP function to perform both a vertical and horizontal match. In this case, it will first look for Gross Profit in column B, then look for Qtr1 in the top row of the table (range C5:F5), and return the value at the intersection of the two. This is similar to using the INDEX and MATCH functions in conjunction. You can also use XLOOKUP to replace the HLOOKUP function.

The formula in cells D3:F3 is:
Comment 6 Óvári 2020-02-02 06:54:23 UTC
Created attachment 157595 [details]
Example 6

Example 6

This example uses the SUM function, and two XLOOKUP functions nested together to sum all the values between two ranges. In this case, we want to sum the values for grapes, bananas, and include pears, which are between the two.

The formula in cell E3 is:

How does it work? XLOOKUP returns a range, so when it calculates, the formula ends up looking like this: =SUM($E$7:$E$9). You can see how this works on your own by selecting a cell with an XLOOKUP formula similar to this one, then go to Formulas > Formula Auditing > Evaluate Formula, and press the Evaluate button to step through the calculation.
Comment 7 Julien Nabet 2020-02-02 08:26:27 UTC

*** This bug has been marked as a duplicate of bug 127293 ***