Excel syntax for VLOOKUP(): |
---|
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
Excel syntax for CHOOSE(): |
---|
=CHOOSE(index_num, value1[, value2][, value3][, …]) |
Syntax for VLOOKUP-CHOOSE |
---|
=VLOOKUP(lookup_value, CHOOSE(index_num, value1[, value2][, value3][, …]), col_index_num, [range_lookup]) |
CHF– the Swiss Franc– will be used in D7 to show how VLOOKUP-CHOOSE works to rewrite nested IF()s.
Rewrite #1 for VLOOKUP()
showed how convenient VLOOKUP()
is compared to the nested IF()
s it replaces. However, Rewrite #3 for INDEX-MATCH also noted that VLOOKUP()
has a limitation:
VLOOKUP() cannot lookup values in columns to the left of lookup_value . |
---|
When VLOOKUP()
is used as written, that is true.
The limitation appears to be caused by table_array
, since that determines how the lookup is to take place.
What if table_array
can be redefined to be able to let VLOOKUP()
look left?
CHOOSE()
is used here to redefine table_array
so that it can look left while it behaves as if it looks right.
As a reminder, this is how CHOOSE()
is normally used:
=CHOOSE(index_num, value1[, value2][, value3][, …]) |
---|
Below are the modifications to make to CHOOSE() before it can be used by VLOOKUP() to look left:
CHOOSE() item | Normal Data | Modification for VLOOKUP() |
---|---|---|
index_num | Single cell or value | array of 2 values |
value1 | Single cell or value | range or column reference |
value2 | Single cell or value | range or column reference |
Based on those modifications, this is the revised syntax for VLOOKUP-CHOOSE:
REVISED syntax for VLOOKUP-CHOOSE |
---|
=VLOOKUP(lookup_value, CHOOSE({1,2[, …]}, desired_column1, desired_column2[, …]), col_index_num, [range_lookup]) |
For purposes of showing VLOOKUP-CHOOSE in action, a 3rd column was added to the Exchange Rates table. This 3rd column is just a duplicate of Column 1. This means the rates to be found in Column 2 are located to the left Column 3.
The index_num of CHOOSE()
to be used here is an array of 2 numbers (1 followed by 2). After this array are the ranges or columns to be presented for use by CHOOSE()
to VLOOKUP()
.
The first range or column to be listed in CHOOSE()
— regardless of its order on the screen– will be assigned 1 from the array defining index_num
. The second range or column will be assigned 2. When VLOOKUP()
goes to table_array
to use the column designated by CHOOSE()
, it will choose the range or column mapped to the number 1.
VLOOKUP-CHOOSE for finding the rate for CHF (the Swiss Franc) should look like this:
=VLOOKUP(B7, CHOOSE({1,2}, $I$3:$I$10, $H$3:$H$10), 2, FALSE) |
---|
Note: |
---|
For Excel 2010 and later, entire columns can be referenced, so for those versions CHOOSE({1,2},$I:$I,$H:$H) would be OK. I used Excel 2003, so it was necessary to use actual ranges limited to cells in the table. |
This excerpt is taken from 8 Ways To Rewrite Nested IF() Functions at Magna Carta XLS Communications. Each of the 8 nested IF()
rewrites from that post will be featured in its own post here.
Other posts will be migrated to this blog before I begin writing posts natively here.