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:
- ISERROR: Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!)
(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.