Use INDEX and MATCH functions for table lookup

 

The table above lists some internet Top Level Domains (TLD) and their equivalent country codes.

How can we look up the country name from the table if we know its TLD? E.g. we have the code “cn” and we want to know the corresponding country name.

We can achieve this by using the INDEX and MATCH functions to search for a specific TLD code and pick up the corresponding country name from the adjacent column,  as follows.

The INDEX function, syntax:   INDEX(array;row_no, column_no). Find details here:

http://www.exceldigest.com/myblog/2008/07/16/how-to-use-the-index-function/

It takes three arguments:  array or table, row no. and column no. and returns the value at the intersection of the row and column.

In this example if we put: =INDEX(A2:B9;4;2). I.e. Table A2:B9, row 4 (of the table, not the row of the worksheet) and column 2, then we will get the country “China”. However we don’t have row no. but we have the TLD code. This is where we need the MATCH function.

The MATCH function, syntax:  MATCH(lookup_value;array;match_type). Find details here:

http://www.exceldigest.com/myblog/2008/07/03/how-to-use-the-match-function/

It takes three arguments: lookup value, lookup array and match type and returns the position where the value is found (row no. within the table). So we can use the MATCH function to get the row no. and then feed it into the INDEX function:

=MATCH(“cn”;A2:A9;0). This function will return a row no. of 4.

We can join the two functions together as follows:

=INDEX(A2:B9;MATCH(“cn”;A2:A9;0);2). This will return the country “China” as we have seen before.

Note: you can replace “cn” with the address of the cell which has the TLD code.