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.