What Is Vlookup Used For

HLOOKUP vs VLOOKUP – 5 Key Differences

The LOOKUP part works like an alphabetize folio in a book where nosotros look at the topic to locate its page number. You'll find the LOOKUP functions like HLOOKUP, VLOOKUP, and XLOOKUP in Excel categorized nether Lookup and Reference functions. The LOOKUP function finds a lucifer by comparison cells in a row or column and returning the reply from some other row or cavalcade.

In this guide, we will see a brief overview of the HLOOKUP vs VLOOKUP functions and see how they compare against each other.

You lot'll learn:

HLOOKUP
VLOOKUP
HLOOKUP vs VLOOKUP – Instance
HLOOKUP vs VLOOKUP – Key Differences

HLOOKUP

The HLOOKUP function in Excel searches for a value in the tabular array horizontally. The lookup values must appear in the showtime row of the table and movement horizontally towards the right.

Syntax

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

Parameters

lookup_value – Specifies the value to search across the cells.
table_array – Specifies the tabular array range where the lookup_value has to be searched.
row_index_num – It is the position of the row from where to go the result.
range_lookup – It is an optional parameter and returns the exact friction match when FALSE is mentioned and returns the guess match or closest match when True is mentioned.

VLOOKUP

VLOOKUP stands for 'Vertical Lookup'. It searches for a specified value in a cavalcade and returns the result from another column in the same row.

Syntax

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

Parameters

lookup_value – This is the value to search beyond the cells to get the match and render the corresponding value from the other cavalcade.
table_array – This argument takes the starting value and the ending value of the cell where the role has to search.
col_index_num – The value in this argument represents the exact alphabetize of the column to look into.
range _lookup – It accepts two values, one is Truthful, and another is FALSE.
- The part returns the closest match if TRUE is mentioned.
- The office returns the exact match if Simulated is mentioned.

This parameter is optional and returns Truthful past default if it is non mentioned in the formula.

HLOOKUP vs VLOOKUP – Example

In the case in a higher place, the HLOOKUP formula takes the lookup_value A2 which is the country, US. The HLOOKUP office moves horizontally starting from the first row to the row number that you lot mention. The second parameter is the table_array which takes the ranges of cells across which the data is spread.

The tertiary parameter takes a numeric value that mentions the position of the row from the first row. Hither, it is the value iii, which refers to the third row from A2. The role returns the value Nihon.

Similarly, the VLOOKUP formula can be parsed to check how it executes. Employ the VLOOKUP formula when you lot move from one column to another, vertically. In the example above, the lookup_value is A4 and the value to fetch is in the third column.

Mention the formula as, =VLOOKUP(A4,A2:D6,three,FALSE). The VLOOKUP formula takes the lookup_value, A4, and moves vertically three columns towards the right and picks up the value there, and returns it. Remember that the VLOOKUP formula cannot move backward from right to left.

A note of caution when y'all use the HLOOKUP formula is that it searches from top to lesser and cannot search from bottom upwards. Besides, it searches from the starting prison cell of the table and non from anywhere within the table.

Allow's await at the case below. The HLOOKUP formula takes A4 every bit the lookup_value. The table_array is mentioned equally A2:D6, and the row_index_num is mentioned equally 2. The function starts to look for a value in the 2nd row and must return equally the UK, but it returns every bit #North/A.

The reason is that the HLOOKUP part looks at the lookup_value A4 and wants to keep downward. But the table_array parameter mentions the starting cell from A2, which requires the role to move upwards which HLOOKUP cannot practise. Hence, information technology returns the message #N/A.

HLOOKUP cannot search from the bottom to the top — *HLOOKUP cannot search from the bottom to the height*

If your search starts from the heart of the tabular array, then mention the formula as follows. =HLOOKUP(A4,A4:D6,ii,Faux). You lot can run across that the table_array is also changed according to the lookup_value to make the formula search from A4 which it considers as the starting of the table.

HLOOKUP vs VLOOKUP – Key Differences

	HLOOKUP	VLOOKUP
one.	It looks for values horizontally across the table.	It looks for values vertically beyond the tabular array.
2.	HLOOKUP is useful when the data is bundled row-wise.	VLOOKUP is used to compare and choice data bundled in columns.
3.	This function searches from the summit to the bottom and it cannot search the bottom-meridian.	It searches from left to right and non in opposite.
4.	Information technology returns the value from the same cavalcade, a few rows apart.	It returns the value from the same row but a few columns apart.
5.	HLOOKUP is not widely used.	VLOOKUP is extensively used.

Let's Wrap Up

In this commodity, we learned almost HLOOKUP vs VLOOKUP functions forth with some examples, and the central differences between them. VLOOKUP is used more extensively than HLOOKUP because of mutual information arrangements. At that place is also a new function, called the XLOOKUP, which is a combination of HLOOKUP and VLOOKUP. Just, XLOOKUP is just bachelor in Excel for Part 365. Read our detailed guide on XLOOKUP vs VLOOKUP to learn more.

We hope you plant this article useful. Please let us know if you face any difficulties while working with the LOOKUP functions. We are happy to help you.

Please visit our free resources section for more than high-quality Excel guides.

Ready to upskill your Excel knowledge? Simon Sez Information technology has been teaching Excel for over ten years. For a low, monthly fee you tin can get admission to 130+ preparation courses. Click here for avant-garde Excel courses with in-depth training modules.

Simon Calder

Chris "Simon" Calder was working every bit a Project Manager in IT for i of Los Angeles' most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Projection from a giant textbook and hated every moment of information technology. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, every bit they say, is history!