Using array formula techniques with the MATCH function for complex lookups

If you've worked with the various LookUp functions in Excel, you know how …difficult it is sometimes to match up data. …We've got two different lists here and they could easily be, and might well be …on different worksheets, possibly even different workbooks. …And in the list on the left we're trying to match up data. …It's already been done, we don't need array Formulas here. …But let's take a look at it. We've got a situation where we've got …last names and first names in separate columns. …We're trying to match them up with the data that we currently see over in column G. …And so, the formula in cell D2 is doing something that you might have seen.…

We're actually combining the data. For example, in the first case here, …we're taking the last name, which is Randall, then putting in a comma and a …space and combining with the first name, Yvonne. …And in effect, this data here is equal to Randall, comma, space, Yvonne. …Then we're using that to look up data in column G. …And once we find it, then we're going into column H to pick up the ID. …