Choose vs VLOOKUP in Excel
Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
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
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.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.