Welcome to Excel Jet Consult blog post. This Excel tutorials talks about using the CHOOSE & XLOOKUP Functions to choose and lookup values from different workbooks.
Video
Sample Data
In the caption below, we have two different workbooks we want to work across. The first caption is about Customer Quote where we are going to use the CHOOSE & XLOOKUP Functions to choose and retrieve values from the source workbook using appropriate Index Number and Lookup Values.
The second caption below contains the values we want to choose and lookup from
CHOOSE Function
Syntax
CHOOSE(index_num, value1, [value2], …)
The CHOOSE function syntax has the following arguments:
- Index_num Required. Specifies which value argument is selected. Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254.
- If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
- If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
- If index_num is a fraction, it is truncated to the lowest integer before being used.
-
Value1, value2, … Value 1 is required, subsequent values are optional. 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text.
In cell C5 of the XLOOKUP workbook, execute the formula:
=CHOOSE([@[PM Code]],'[Price List.xlsx]International Price List’!$F$4,'[Price List.xlsx]International Price List’!$F$5,'[Price List.xlsx]International Price List’!$F$6,'[Price List.xlsx]International Price List’!$F$7)
Click Enter
The CHOOSE Function, choose each of the Payment Method based on the index number. For instance, Index Number 1, returned MasterCard, index number 2 returned Visa Card and so on
From the caption below, corresponding Payment Method was chosen and returned based on the specified index number in column B
XLOOKUP Function
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Description |
| lookup_value
Required* |
The value to search for
*If omitted, XLOOKUP returns blank cells it finds in lookup_array. |
| lookup_array
Required |
The array or range to search |
| return_array
Required |
The array or range to return |
| [if_not_found]
Optional |
Where a valid match is not found, return the [if_not_found] text you supply.
If a valid match is not found, and [if_not_found] is missing, #N/A is returned. |
| [match_mode]
Optional |
Specify the match type:
0 – Exact 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 where *, ?, and ~ have special meaning. |
| [search_mode]
Optional |
Specify the search mode to use:
1 – Perform a search starting at the first item. This is the default. -1 – Perform a reverse search starting at the last item. 2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. |
In cell E5 of the XLOOKUP Workbook, execute the formula
=XLOOKUP([@[Product ID]],’Price List.xlsx’!Item_Code,’Price List.xlsx’!Table1[Product])
Click Enter
In cell F5 of the XLOOKUP Workbook, execute the formula
=XLOOKUP([@[Product ID]],’Price List.xlsx’!Item_Code,’Price List.xlsx’!Table1[Unit Price])
Click enter
Bothe formulas above returns the Product and Unit Price from the Price List workbook. Note that the source and destination workbooks are formatted as Excel Tables
See you in the next tutorial