How to use hlookup and vlookup?

• We will click on Cell G5.
• We will insert the formula below into Cell G5. =VLOOKUP(G3,A5:E10,HLOOKUP(G4,B3:E4,2,FALSE),FALSE)
• We will press the enter key.

VLOOKUP and HLOOKUP are two of the most popular formulas in Excel and using them together is one of the first formula combinations that people learn.  While using INDEX MATCH for vertical lookups and INDEX MATCH MATCH for matrix style lookups are superior approaches, it’s still a good idea to learn this formula combination and add it to your toolkit of lookup approaches.  You may find yourself in a situation where you encounter this formula combination after inheriting someone else’s file, or you may just be working with someone who does not understand INDEX MATCH.  Both of these contexts make it worthwhile to learn VLOOKUP HLOOKUP.

There are two key requirements for using VLOOKUP HLOOKUP.  The first is that you need to have your data formatted in a matrix.  Or, to put it in simpler terms:

Your data table needs to have lookup values on both the top and left hand side.

In the example below, we have Country as a potential lookup value down the leftmost column and Year as a lookup value across the headings at the top.  Your goal should be to find the intersection between these two lookup arrays for particular values (for our example, we’ll be looking up the revenue amount for “Brazil” in “2014“)

The second requirement is unique to the VLOOKUP HLOOKUP combination formula:

You’ll need to add an additional row below your column headings to label the column numbers in your table

This requirement is specific to VLOOKUP HLOOKUP and is not a requirement of INDEX MATCH MATCH.  If for whatever reason you cannot insert a row identifying the columns of your data table, use INDEX MATCH MATCH instead.

= VLOOKUP ( lookup_value , lookup_array , HLOOKUP ( lookup_value ,  lookup_array , 2 , FALSE ) , FALSE )

The syntax for VLOOKUP HLOOKUP is fairly simple conceptually.  If you understand both VLOOKUP and HLOOKUP, all you are doing is replacing the column_reference component of VLOOKUP with the HLOOKUP formula.  We’ll go into detail about both formulas below.

VLOOKUP will represent the foundation of your formula combination.  The syntax for VLOOKUP is as follows:

= VLOOKUP ( lookup_value , lookup_array , col_index_num, [range_lookup] )

HLOOKUP has almost the exact same syntax and structure as VLOOKUP, with the exception that the lookup is occurring horizontally and therefore, you need a row index number instead of a column one.

= HLOOKUP ( lookup_value , lookup_array , row_index_num, [range_lookup] )

As mentioned before, the purpose of the HLOOKUP formula in this combination is just to provide the column reference for VLOOKUP to use.

Goal:  Assume we want to find the Revenue amount for “Brazil” in the year “2014”

Step 1:  Insert a row right below your field headings that identifies the column numbers of your table.  This was the second baseline requirement mentioned for VLOOKUP HLOOKUP and is highlighted in green below.

Step 2:  Start writing your VLOOKUP formula and select your vertical lookup value, in this case, the country “Brazil”

Step 3:  For the table array, select the portion of your table excluding both the top row and the additional row you just added (Please note: if you were instead to select the whole table, the formula combination will still work; just make sure that you have the left hand side of the your selection lined up with the edge of the table)

Step 4:  Once you get to the column reference, start your HLOOKUP formula and select your horizontal lookup value, in this case the year “2014”

Step 5:  For the table array, select the mini table you created when you inserted a row to identify the column headers.  This table should only be two rows in terms of height.

Step 6:  For the row reference, input 2.  This is because your mini table only has two rows, and you want it to return a value from the second row.

Step 7:  Close out both your HLOOKUP and VLOOKUP formulas with FALSE for the range lookup clause.  This is because we want an exact match for both of our lookups.

The first thing Excel does is process the formula from the inside out, thus addressing your HLOOKUP formula first.  The HLOOKUP portion is fairly simple because we only have a table with 2 rows.

Moving horizontally across the top of the table array selected (green border table), stop at the lookup value (“2014“) and return the value in the corresponding second row (row_index = 2)

In this case, the formula resolves to 4.  Therefore, 4 will be used as your column reference for your VLOOKUP.

Next Excel will process your VLOOKUP formula.

Moving vertically down the left side of the table array selected (red border table), stop at the lookup value (“Brazil“) and return the value in the corresponding fourth column (result of HLOOKUP = 4)

Let’s explore HLOOKUP vs VLOOKUP in Excel, and find out exactly how and when to use each.

The HLOOKUP and VLOOKUP functions are grouped in the lookup category of functions. The function looks for a certain value in the first row (HLOOKUP - (h)orizontal) or column (VLOOKUP - (v)ertical) of a table and returns any value from the same column (HLOOKUP) or row (VLOOKUP).

In the example below, we are using the Member ID field in the Workshop Registrations dataset to find the same Member ID in the Members dataset, to return the First Name linked to the ID.

A VLOOKUP will be used if the Member IDs are in a column.

The HLOOKUP will be used if the Member IDs are listed in a row.

Lists are usually in columns, hence VLOOKUP is used more often in practice than HLOOKUP.

Quick Tip: If you have access to Microsoft 365, keep an eye out for the newer and improved XLOOKUP function. XLOOKUP is not available to all versions of Excel yet, but if you think H/VLOOKUP is handy, you'll want to learn about XLOOKUP. Check out our XLOOKUP resource for more information and examples of this new function.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

=HLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Lookup_ value:

Table_array:

Col_index_num:

Range_lookup:

In the example below, member IDs are spread across rows with details down the column. You will have to use a lookup that searches horizontally - HLOOKUP.

To find the first name of the member for the workshop registration, you would add an HLOOKUP to the column B cell to find the member’s first name from the member’s information below.

In Excel, the HLOOKUP takes the member ID (marked yellow) from A4 and compares it to the first data in the top row of the table array (blue area).

When it finds the member ID in B24, it can now give you any value listed below B24 as long as it is in the defined blue area (B24:L27). The search is not case sensitive, but it is character sensitive.

In the second example, the member IDs are listed in column H (H4:H14) and the relevant information for each member is listed in the row next to this. If the data you want to search in is in a column, a VLOOKUP is needed because you want to search vertically.

The member ID is in A4 (yellow) and the table array is defined in blue (H4:K14). Excel looks for 12658 in the first column of the table array (blue) and finds Steve in I4.

Now Excel can return any value in row 4 as long as it is in the blue area range. The blue area columns are numbered (see the red letters at the top) and this is the number required for the returned value.

The function will look for something exactly like the lookup. If it cannot find something exactly alike, a #N/A will be returned. This will cause no returns if the one value has additional space characters in the cell, or if one value is a number and the search column contains numbers stored as text.

To solve this problem, wildcard characters may be employed. These qualify as wildcard characters:

The ? would replace one character and the * takes the place of 0 or multiple. Wildcards work with exact matches only.

This function will return either the exact match or the value just smaller than the one it is looking for. Data in the search column has to be sorted in ascending order for the approximate to return values you require. Including headings in your table may also (in very few instances) give you incorrect returns, so exclude them just to be sure.

The limitation of the returned value row (HLOOKUP) or the returned value column (VLOOKUP) position in relation to the lookup value (either below or to the right) poses a problem where the returned value is above or to the left of the lookup values.

This can be overcome by combining a CHOOSE function with the HLOOKUP or VLOOKUP, or by using the INDEX and MATCH function.

The CHOOSE function changes the columns around so that the H/VLOOKUP thinks it is searching left and returning columns to the right without actually changing the data.

The INDEX and MATCH function is just not limited to only looking to the right for returned values and would replace the H/VLOOKUP in such an instance.

These lookups search from left to right (HLOOKUP) or from top to bottom (VLOOKUP). This may slow your spreadsheet down a little  - the bigger the sheet, the more speed may be an issue.

Recent Microsoft updates focused on the speed issue and it may not even be an issue still or remain one for long.

Because of the linear search method for these VLOOKUPs, should the search field have duplicate fields, Excel will return only the first value found and will ignore all others. These functions will work best in search fields that contain unique values.

#N/A - the value searched for cannot be found - only in exact matches.

In the example below, this would display if you looked for a number (25) that does not exist in the list.

If you want a customized message here, you can add an IFERROR to the VLOOKUP and use the return value argument in IFERROR to add a message like “The value is not found.”

#Ref - the returned field required is outside of the defined area.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VLOOKUP vs HLOOKUP (wallstreetmojo.com)

The VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more function is used when we have a set of vertical data. It allows us to search a range of data references with columns, and retrieves the right information we are looking for.

The syntax of the VLOOKUP formula is,

The arguments of the VLOOKUP formula are,

The HLOOKUPHLOOKUPHlookup is a referencing worksheet function that finds and matches the value from a row rather than a column using a reference. Hlookup stands for horizontal lookup, in which we search for data in rows horizontally.read more function is used when we have a set of horizontal data. It allows us to search a range of data references with rows, and retrieves the right information we are looking for.

The arguments of the HLOOKUP formula are,

Let us take an example to illustrate VLOOKUP vs HLOOKUP. In the below image, we have a table with the “Employee ID”, “Employee Name”, and “Performance Grade by HR”.

With just the employee ID, we will retrieve the employee’s name and the performance grade given by HR using the VLOOKUP and the HLOOKUP formula.

The output using the VLOOKUP formula is,

The output using the HLOOKUP formula is,

Output Interpretation:

Therefore, the functions VLOOKUP and HLOOKUP retrieved the same result, but the table is arranged differently.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VLOOKUP vs HLOOKUP (wallstreetmojo.com)

The key differences are as follows: