Excel syntax for VLOOKUP(): |
|---|
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
Just from the Excel syntax it's obvious that VLOOKUP() is easier to use than the 8-level-deep nested IF() used for ZAR:
Referring to the tables at the top of this post, these are the values needed by VLOOKUP() in cell D4:
VLOOKUP() item | Cell(s) or range | Value | Comments |
|---|---|---|---|
lookup_value | B5 | GBP | Given in the Foreign Currencies table |
table_array | $G$3:$H$10 | 8x2 table | Given in the Exchange Rates table |
col_index_num | N/A * | 2 | Column 2 of the 8 row by 2 columns Rates table |
[range_lookup] | N/A | FALSE | Exact match needed |
The VLOOKUP() used in D4 should look like this:
An example of VLOOKUP(): |
|---|
=VLOOKUP(B4, $G$3:$G$10, 2, FALSE) |
By default, VLOOKUP() looks for an approximate match. This means the optional parameter [range_lookup] is automatically set for TRUE and can be omitted. Here an exact match is needed, so we have to override the default match by specifying FALSE. (If FALSE is too much typing, 0 works just as well; whichever you use, be sure to use it.)
// Comment: For VLOOKUP(), 0 is equivalent to FALSE.: |
|---|
=VLOOKUP(B5,$G$3:$G$10,2,0) |
VLOOKUP() will use "GBP" in B5 of the Foreign Currencies table to determine its corresponding Rate in the Exchange Rates table.
Column 1 of table_array contains 3-character currency codes. Once GBP is located in Column 1, VLOOKUP() uses col_index_num— Column 2 from table_array to pull its corresponding Rate, 1.368300.
The Rate for GBP calculated by VLOOKUP()-- either version as shown above-- matches its entry in the Rate table.
| NOTE: |
|---|
Remember the "N/A *" for col_index_num? That's to indicate that the value doesn't need to be hard-coded into the formula. It can be calculated or determined by using the MATCH() function. Later examples will show how MATCH() is used. |
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.