Forum Discussion
Excel formula for lookup
We have two sets of data to compare. In one set of data, cell A1 has numbers separated by commas, like this:
13010982231, 13010982232, 13011011681, 13011011682
Cell A2 has the name of the person, example, James, and James is considered to be associated to those numbers.
DATA SET1
Number Name
| 1301086213 | Jim |
| 1301088551 | Joe |
| 1301058852 | Melinda |
| 1301032770 | Melissa |
| 13010750451 | Billy |
| 1301078972 | Johnny |
| 1301096578 | Abigal |
| 1301055408, 1301055619, 1301055901 | Chris |
In the other data set, we just have one number in a cell and we want to check and see if that number in the cell can be found in the list of numbers in Column A from the other data set1, and if it is found, tell us the name of the person that number is associated to in that data set and fill that name into the name column in DATA SET2.
DATA SET2
| Number | NAME |
| 13010914141 | |
| 13010914142 | |
| 13010821991 | |
| 13010821911 | |
| 13011037061 | |
| 13011037062 | |
| 13010979211 | |
| 13011026881 | |
| 13011032761 | |
| 13011032762 | |
| 13011032763 | |
| 13011032764 | |
| 13011032765 |
We used to be able to do this using VLOOKUP, because there was only one number in the cell, but now that the cell contains multiple numbers, it doesn't work as expected. We are able to lookup some of the information by using a vlookup formula similar to this, =VLOOKUP("*"&G2&"*",DATA,2,FALSE) but the results do not always populate.
