# Using CHOOSE and XLOOKUP Functions to Choose & Lookup Values Across Different Workbooks

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

Choose Function uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.

## 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

Posted in Blog.