How to use column header labels in Vlookup for more interesting and accurate data analysis in Excel. Watch the video below:
How can we use the column headers in Vlookup and what is the advantage of doing so? Let’s first have a look at the Vlookup function. We will now find the corresponding amount for our product RAM4 in our data using the Vlookup function like so:
We are now looking up for value RAM4 in cell A5 from our complete data also known as table_array the amount which is in column 2 and wish to get an exact match by using FALSE or 0 (zero). We get the correct amount of 780000 for RAM4. Instead of using column index 2, if we use the header “Amount” and keep rest of the parameters same as before we get an error “#VALUE”. This strategy doesn’t work. Now how can we achieve our goal? We can use the column headers by taking the help of the MATCH function. The match function can help by providing the position of the header. So our Vlookup function would now look like this:
=Vlookup( A5,A1:C51,MATCH(“Amount”, A1:C1,0)FALSE)
Instead of the header “Amount”, we can use another header like “Store” to get the corresponding value of the store in which this product RAM4 is available. So, you may ask – where is the advantage? Well, if you add another column between the headers “Product” and “Amount” we would still get the correct value for the amount. In fact, we can interchange the complete columns “Amount” and “Store” and still get the correct result for the amount since the headers remain intact. We can use numbers in the match function but if columns are added or interchanged we will not get the expected values.