Look up values in Excel

Formula explanation clarified 22-May-08.

I recently had the need to compare a long list of values with another list, and for the matches get a value from the same row. For example, if you have a long list of phone numbers and you want to find who they belong to, you could compare the phone numbers to a contact list, and for every match pull out the name. Oh, and if there is no match, you don't want to get an error message, but rather something more intelligent.

It takes 4 Excel functions to do this:

1. IF(logical_test,value_if_true,value_if_false)
   
2. ISERROR: Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!)
   
3. INDEX(array,row_num,column_num)
   
4. MATCH(lookup_value,lookup_array,match_type)

Here's how to do it:

=IF(ISERROR(INDEX(phone!$B$1:$B$25,
MATCH(A8,phone!$A$1:$A$25,0))),"NA",
INDEX(phone!$B$1:$B$25,MATCH(A8,phone!$A$1:$A$25,0)))
(I used carriage returns for display, this should be one continuous string)

• phone!$A$1:$A$25 refers to the list of Phone numbers in my contact list (the tab name in my spreadsheet with this data is named "phone").
  
• A8 is the particular cell that I am trying to match against the data in phone!$A$1:$A$25 -- When I start the formula, I am in B2, so this would be A2.
  
• phone!$B$1:$B$25 refers to the list of Names in my contact list. When I find a match in phone!$A$1:$A$25, it takes the corresponding cell from phone!$B$1:$B$25 and puts the value in the cell of the formula.

I use the IF statement to check for no matches using ISERROR and the first use of the INDEX function. If there is an error, then the cell gets populated with NA. If there is no error, then the second INDEX function is used. With the second INDEX function, it calls the range to match against with phone!$A$1:$A$25; it uses the MATCH function then to populate row_num, col_num arguments.