Skip to content

Choose vs VLOOKUP in Excel

It’s easy to get mixed up between two seemingly similar Excel functions – Vlookup() and Choose() . Here’s how to, er, choose between the two.

Choose( index_num, value1, [value2], [value3].. ) lets you select one item from a list within the function.

For example: Choose(2, “High”, “Middle”, “Low”) will return the second list item; “Middle”.

The index number (first argument) has to be a number from 1 to 254.  Fractions are truncated (eg 2.5 is treated as 2).  Of course, you can use a cell reference.

The list of items (second argument onwards) can be a fixed item or a cell reference. For example: For example: Choose(2, A2:A5, B2:B5 , C2:C5) will return B2:B5.

What doesn’t work is a reference to a list of items.  Check out this simple table and attempt to use Choose.

You might hope that =CHOOSE(2,Floors[Names]) would choose the second item from the Names list (ie Low) but no.  Choose won’t accept a range of cells in that way (ie Choose(2,D2:D6).  They’d have to be individually listed eg =CHOOSE(2,D2,D3,D4,D5,D6)

Vlookup() is the function you need with a lookup column added.

=VLOOKUP(2,Floors,2,TRUE) looks for the item “2” in the first column of the Floors table then chooses the second column alone.

For more see David Goodmanson’s features on Choose() ,  Vlookup() and Calculating with Vlookup for more info.

About this author

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.