Ah, the Vlookup() – the staple of data linking since Excel was a child – 40 years ago…I’ve done a tutorial on it here but I want to explore how to make it more flexible . Let’s say you have a scenario where you need to pull in data and then copy it across but the columns in your formula sheet are not in the same order as your table_array.

One way to get around that is to use the Match() function to generate the number of a matching column and to use that instead. This does have the limitation that your column names must be exactly the same.

Let’s begin with a tutorial on the Match() function and then let’s see how we would weave that into our Vlookup(). One small point about the Match() function – the number generated is the number in the list the item is. You can download the file here.

Now the next phase is around how you would use this with a Vlookup(). I am assuming familiarity with the Vlookup() function. Here is the file I used and here is a video that shows you how to use it.