Vlookup will only lookup vertically and Hlookup will only lookup horizontally. So if you combine these two lookups you will get a matrix type lookup. By using matrix lookup, you can pinpoint the data with the column heading as well as row heading. It is worth learning this combination because some person may handover the file with this type of combination. Or you may be asked to prepare a matrix lookup because your boss or colleagues may understand this combination. Keep in mind there is another combination called INDEX MATCH MATCH which serves the same purpose. We will learn this combination in Chapter 7 and is much superior to Vlookup Hlookup. Syntax: VLOOKUP ( lookup_value , lookup_array, HLOOKUP ( lookup_value , lookup_array , 2 , FALSE ), FALSE ) If you know Vlookup syntax, you can see the third parameter of the Vlookup is a Hlookup formula. Now we will go through an example. In the image we have the students name as column headings and their subject in rows and their scores displayed under each name against each subject. This type of arrangement is called a matrix, and by using this combination, you can easily fetch data like how many marks Lucy got for Marketing. [example] Example: Open the file Chapter 6.1 - VlookupHlookup.xlsx To make this combination work we have to add one row after the student names specifying the column number for the third parameter of the Vlookup. Then we use Hlookup formula to fetch this column number you have created. It will look like this after the addition, [example] Now enter the formula =VLOOKUP(C9,A3:G7,HLOOKUP(C10,B1:G2,2,0),0) in C12 cell. C9 cell has the subject, and the same is compared to the data in the table array A3:67. When you want to retrieve data matching subject as well as name then you have to get the column number where the name is. So for that, we have added the additional row specifying the column number of the names. Now put Hlookup as third parameter inside Vlookup function to get the column number for whatever name entered in C10 cell. So for John, the column number is 2, Hary it is 3 for Lucy it is 4. So here we have hardcoded the column number as a row and used the Hlookup function to fetch that data. Then that data is used as the third parameter of the Vlookup to fetch the actual data.
Vlookup Match combination is also used for creating matrix lookup. But it is better than Vlookup Hlookup combination we have just learned. Here we don't have to create an additional row specifying the column number. Also, another advantage is if you insert or delete a column it will still give the correct answer because of the Match function. So it is better than Vlookup Hlookup combination. In other words, the formula becomes dynamic, so you can insert or delete columns whichever way you want. If the Match function finds a match, it will fetch the data. But keep in mind Vlookup will always lookup from left to right. If you add any additional columns to the right of the table array, it will not be taken into consideration automatically. If you want to remove this limitation, then you have to use INDEX MATCH MATCH. Syntax: VLOOKUP ( lookup_value , lookup_array, Match function , FALSE) Here we are using Match function as the third parameter of the Vlookup to get the column number of the names. In the Hlookup combination, we have manually added a row to specify the column number. But here Match function will provide the column number, and there is no need to add a row. Ok, first we will go through the Match function to find out how it works. Syntax: MATCH ( lookup value , lookup_array,[match_type]) Match function will return the position value of the data, not the data itself. For Example, you have four names from A1 cell to D1 like in the image. [example] Now if you want to get the position of Hary within the array (cells A1:01) you have to write the Match formula like this MATCH(B1,A1:01,0). The first parameter is the lookup value Hary, and it is in cell B1. Now the second parameter is the array (A1:D1) and third parameter we can put false or 0 for an exact match. So here we get the answer 2 because Hary is second in the cell range A1:D1. As you can see Match function always return a number that we can use it as the third parameter in Vlookup. Example: Open the file Chapter 6.2 - Vlookup Match.xlsx Enter this formula in cell C11 =VLOOKUP(C8,A2:G6,MATCH(C9,B1:G1,0)+1,0). Here we have given the first parameter as cell C8. So whatever subject we enter in C8 ill be compared to the second parameter in the cells A2:46. As third parameter, we have given the Match function. This will match the name you provide in the C9 cell with the array B1:G1 and will give back the position number of the name. Here we have added one to match function to compensate for the column you have not included. The actual column number of the name John is two. But here the Match function starts from B column and it will return the column number one less. So to correct that I have added one. Otherwise, Vlookup will fetch the wrong column. If you don't want that +1, you can rewrite the formula like this, =VLOOKUP(C8,A2:G6,MATCH(C9,A1:G1,0),0). Here the Match functions second parameter starts from A1 column and you will get the correct column number.