Choose vs VLOOKUP in Excel


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

choose vs vlookup in excel microsoft excel 18485 - Choose vs VLOOKUP in Excel

=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.

subs profile e1563205311409 - Choose vs VLOOKUP in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address