vlookup.co
Learn VLOOKUP HLOOKUP and INDEX MATCH In-depth

VLOOKUP Combinations

01 VLOOKUP HLOOKUP

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.

02 VLOOKUP MATCH

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.

02 VLOOKUP to look left using choose function

As you know, the Vlookup function cannot do a left lookup. But you can force the Vlookup to look left by clubbing it with Choose function. Let us first go through the Choose function. Syntax: CHOOSE(index_num, value1, value2, value3.....) For example, this formula CHOOSE(1,"RED","GREEN") will return the answer RED. Since we have given the index number as 1 you will get the first value specified in value1. Now if you want GREEN you have to give 2 as first parameter as simple as that. Now here we have given the color name directly in double quotes as values. Instead, you can give a cell range as value1 and value2 and the first parameter index number we can give in curly brackets like this CHOOSE({1,2},A2:A20,B2:B20). Now we have given the two index numbers in curly brackets; Choose will assume the first column range is from A2:A20 and second column range is from B2:B20. Now we can rearrange the formula like this CHOOSE({1,2},B2:B20, A2:A20) and now the first column range will be B2:B20 and second column range will be A2:A20. We will be using the Choose function like this to do the left lookup with Vlookup function. Example: Open the file Chapter 6.3 - VlookupChoose.xlsx Here we have the name of the student in B column and their score in A column. Now we will write the Vlookup formula with Choose function. [example] Enter this formula in C9 cell =VLOOKUP(C8,CHOOSE({1,2},B2:B6, A2:A6),2,0) Student name John is in C8 cell so we will supply the first value as C8. Then as second parameter we will give the Choose function and will give the Index number in Curly brackets to denote the order of the range. Next, we are giving the second and third parameter of the Choose function as cell ranges used in the Vlookup function. Here we have given the B column range first and A column range second. This will trick the Vlookup function into believing B column is the first column in table array. Now it is very easy to retrieve data since Choose has rearranged the column you can now specify the number 2 in the third parameter to get the Score.

02 Case Sensitive VLOOKUPS

As you know, Vlookup is case insensitive. What it means is it will not differentiate between Name or NAME or Name. Each word is treated as same. But if you want to do a case-sensitive lookup you have to make use of EXACT formula. What Exact formula does is it will compare two text values and then return true for matching cases and false for not matching. For example, Exact(“Apple”,"Apple") will return True and Exact(“Apple”,"APPle”) will return false. To do a case-sensitive Vlookup, you have to write a nested formula like this. IF (EXACT ( Lookup value ,First VLOOKUP), Second VLOOKUP, "No Match") We have used an If function and then given the first parameter as an Exact formula. The exact formula is comparing its first parameter with the first Vlookup. If the Exact formula returns True, it will execute Second Vlookup and if it is false, we will get the text No Match. Instead of No Match, you can write any word or any formula. Here the part where I have written Second Vlookup is the actual Vlookup formula. Now we will go through an example. Example: Open the file Chapter 6.4 - VlookupCaseSensitive.xlsx [example] =IF(EXACT(D8,VLOOKUP(D8,A2:A6,1,0)),VLOOKUP(D8,A2:F6,5,0), "No Match") Enter the above formula in D10 cell. Here we have started with If function with first parameter Exact Function. The Second parameter we have given the actual Vlookup formula and third parameter the text No Match if there is no match. You can write any text of your choice. Now we will explain each part separately. (EXACT(DS, VLOOKUP(D8,A2:A6,1,0)) - Exact function will compare the first parameter with the value returned from the Vlookup formula. If there is an Exact match, then Vlookup will return True. VLOOKUP(D8,A2:F6,5,0) – This is the actual data we want to fetch if there is a match. "No Match" - This is the text we want if there is no exact match. Exact function is comparing the name Hary in the cell D8 with the names in A column. Since Exact column cannot lookup into a range, we use a Vlookup to compare the name in cell D8 with the names in A2:A6. Now Vlookup will fetch the data from the range irrespective of the case and pass it to the Exact functions second parameter. If there is no match, anyway it will return N/A error and that will be displayed. If it founds a match, the Exact function will give True and false if it is not matching. So you will get the correct value for the exact match and No match for not matching cases (but data is there with mixed case) and N/A for no data. Now enter the formula on your own in E10 and F10.

× DATE: Thu, Feb 06, 2020
× TIME: 9:38:44 pm
× BEHAVIOR:
on Twitter or sign-up for my monthly newsletter:
About
Archive