Excel syntax for INDEX() : |
---|
=INDEX(array, row_num[, column_num]) |
Excel syntax for MATCH() : |
---|
=MATCH(lookup_value, lookup_array, [match_type]) |
Syntax for INDEX-MATCH: |
---|
=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]) [, MATCH(lookup_value, lookup_array, [match_type])]) |
As convenient as VLOOKUP()
is, it has a limitation: it can't look for data to it's left. For that reason, INDEX-MATCH is a more versatile way to rewrite nested IF()
s. EUR in D6 will use INDEX-MATCH.
The MATCH()
portion of INDEX-MATCH is nearly identical to that used for CHOOSE-MATCH. The only change is setting B6 equal to EUR.
Referring to the tables at the top of this post, these are the values needed by INDEX()
in cell D6:
INDEX() item | Cell(s) or range | Value | Comments |
---|---|---|---|
array | $H$3:$H$10 | Rates | Column 2 of the Exchange Rates table |
row_num | See Comments | See Comments | Result from MATCH() is fed to row_num |
[column_num] | See Comments | See Comments | Result from MATCH() is fed to row_num |
Just as multiplication tables are used to find products of 2 numbers, INDEX()
is used to find the value at the intersection of row_num
and (the optional) column_num
. In this case, it’s even easier since only 1 column of the Exchange Rates table is used,so the revised syntax for INDEX-MATCH looks like this:
REVISED Syntax for INDEX-MATCH: |
---|
=INDEX(array, MATCH(lookup_value, lookup_array, [match_type])) |
These are the revised values for INDEX():
INDEX() item | Cell(s) or range | Value | Comments |
---|---|---|---|
array | $H$3:$H$10 | Rates | Column 2 of the Exchange Rates table |
row_num | See Comments | See Comments | Result from MATCH() is fed to row_num |
[column_num] | N/A | N/A | Optional, and not needed here |
Replace row_num
from the INDEX()
below with the MATCH()
above, and the contents of D6 should look like this:
REVISED INDEX-MATCH for D6: |
---|
=INDEX($H$3:$H$10, MATCH(B6, $G$3:$G$10, FALSE)) |
Note that B6 is set to EUR, MATCH()
determined that it is listed 3rd in Column 1 of the Exchange Rates table, and INDEX()
used that detail to determine that its corresponding rate in Column 2 is 1.217170.
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.