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.
![](//3.bp.blogspot.com/_AOsG6-cAzZ0/SCyY0LvkW8I/AAAAAAAANhY/KO70G-QHC28/s400/phone-rec.png)
It takes 4 Excel functions to do this:
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.
![](http://3.bp.blogspot.com/_AOsG6-cAzZ0/SCyY0LvkW8I/AAAAAAAANhY/KO70G-QHC28/s400/phone-rec.png)
![](http://4.bp.blogspot.com/_AOsG6-cAzZ0/SCyY9bvkW9I/AAAAAAAANhg/P2fVh0yk_wM/s400/contact-list.png)
- IF(logical_test,value_if_true,value_if_false)
- ISERROR: Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!)
- INDEX(array,row_num,column_num)
- MATCH(lookup_value,lookup_array,match_type)
=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.
Comments