How to Use the LOOKUP Function with CHOOSE in Excel 2016

As the name implies, the Microsoft Office Excel LOOKUP function is used to look up a specific value located in an index or list and returned the identified value to a particular cell. This can be useful in a variety of problem-solving applications, that require a representation of a specific value or response.

Lookup/Reference Purpose

The LOOKUP function is used to return a value from an array or range (one column or row).

The LOOKUP function is a built in MS Office Excel worksheet function categorized as the Lookup/Reference function. As a worksheet function, LOOKUP, can be used as a formula located in the cell of a worksheet.

Looking Up with the CHOOSE Function

One of the simplest ways to use Excel’s Lookup function, is using the function =CHOOSE. You can type this into a particular cell, or you can find it under Lookup and Reference in your toolbar.

When you select the =CHOOSE function, the Function Argument box appears. Here you can specify a value for a certain response, in a certain number of cells (index_num).

In our example, we want to assign values to specific survey questions. Excel has the option to assign up to 254 values.

For example:

Value1 = “Strongly Agree”
Value2 = “Agree”
Value3 = “Neither Agree nor Disagree”
Value4 = “Disagree”
Value5 = “Strongly Disagree”

While we haven’t entered any values into our table, the Response cell will display “#VALUE!”. This will change as soon as we start inputting our response. When we enter the number 2 in the cell representing our Question 1 response, you can see that the equivalent value in our “=CHOOSE” cell changes to Agree, just like we specified in our arguments.

Note: If you use a value such as 3.6, Excel is automatically set to round down. Therefore the Value3, or “Neither Agree nor Disagree”, will be displayed. If you set a value of .8 Excel will not be able to round down and you will render the “#VALUE!” error. This error will also appear for any numbers not specified in your arguments (e.i. 6, 7, 8, etc.).

Avoiding Error Message with IF ERROR Function

When you are working with several cells or large sets of data, the “#VALUE!” might not be something you want to see across your table. To avoid this, you can use Excel’s IF ERROR function.

To implement the IF ERROR function simple click on the cell with your CHOOSE argument. Type IFERROR before your CHOOSE argument.

Now, you may want to display a message such as “responses must be a value 1-5”, or in other instances you might want the cell to remain blank. If you’d like the cell to remain empty, you will simply enter a space (“ “) at the end of the cell formula.

In our example, the entire cell will look as such:
IFERROR(CHOOSE(C3”Strongly Agree”, “Agree”, “Neither Agree nor Disagree”, “Disagree”, “Strongly Disagree”), “ “).

This means that anything outside of our 1 through 5 values, will leave the cell empty until a correct value is inputted.

Get our complete Microsoft Excel 2016 training courses for beginner, intermediate and advanced learners. Click here to learn more. >>

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!