Excel XLOOKUP and Other Lookup Functions: Create Easier and More Versatile Lookup Formulas with New Powerful Excel Functions (Excel 2019 Mastery Book 5) by George Nathan

Author:George, Nathan [George, Nathan]
Language: eng
Format: epub
Published: 2020-05-09T16:00:00+00:00

value1

Required. The first value is required. Values can be numbers, cell references, ranges, formulas, functions, or text. For this example, we will be using ranges.

[value2], …

Optional. You can add up to 253 additional values.

When we apply the CHOOSE function to our formula, we get the following:

=VLOOKUP(B3,CHOOSE({1,2},$F$3:$F$47,$E$3:$E$47),2,FALSE)

Formula explanation:

=VLOOKUP(B3,CHOOSE({1,2},$F$3:$F$47,$E$3:$E$47),2,FALSE)

Our table_array is E3:F47 and we want to return values in column E. We want a method in our formula that reverses the order of the columns, that is, put column E to the right of column F. The CHOOSE formula can do this:

CHOOSE({1,2},$F$3:$F$47,$E$3:$E$47)

This formula has F3:F47 as the value1 argument and E3:E47 as the value2 argument. Both ranges have been converted to absolute references.

The index_num argument of our CHOOSE function is {1,2}, which tells the function to return the data in the order value1 and value2 , that is, column F before column E.

With the range (E3:E47) as the second column in table_array, VLOOKUP can now lookup values in the first column (F3:F47), and return values from the second column E3:E47.

To populate the other cells in column C, we can fill down the formula. The absolute references we have used means the lookup and return ranges remain the same.

Tip : We can make this formula a little easier to understand by using full column references as long as there will be no other data in the columns apart from the data being used by the formula.

With full column references, the formula would look like this:

=VLOOKUP(B3,CHOOSE({1,2},F:F,E:E),2,FALSE)

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.