Choose vs VLOOKUP in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Give it a try. You can unsubscribe at any time.

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.

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.