Excel Video 65 covers a common problem in VLOOKUP, what to do when there’s a problem with the data you’re looking up. In the video example, the accounting data has an account number and a department number. There are several departments using each account number, so if you try to VLOOKUP based on account number Excel can’t figure out which department you want to look up.
The solution is to concatenate the data, to make two or more pieces of data one unique piece of data that Excel can find. To concatenate in Excel, use the & symbol. If you want to put text between the pieces of data, enclose the text in quotes. Watch the video to see how once you have the concatenate formula right, you can copy the formula to both tables and easily get the unique account number and department number you’re looking for.
