# Excel formula for lookup

Copper Contributor

# 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.