Using array formula techniques with the MATCH function for complex lookups

- When you're trying to match up different lists…and sometimes they are on separate worksheets,…you run into some real problems…when names are entered differently.…We've got a table and columns A, B and C.…And the names are split into column A…for the last name, column B for first name.…There's another table over here…where the names are in a single column.…We're trying to get the sales information…here for Emily Roy.…It's 77,945, we can see it over there…but what do we use to pull that data together?…Function of choice typically here is the VLOOKUP function.…

As you enter a VLOOKUP function,…you need to compare a name or an entry,…like this in cell F2.…We're trying to match up that name…with what we see over in columns A and B together.…There is no way we can really make this work…with VLOOKUP because the second argument…of a VLOOKUP function is the location of a table.…And VLOOKUP will compare this information…with the left column of the table.…And the left column only contains last names,…so there's no way we can really do this with VLOOKUP.…