# HLOOKUP

## “HLOOKUP”

Used for – “HLOOKUP” function in MS Excel is used to looks up a value only in Top row of the selected table or array and returns a value in the same column from a row you specify. Here you should know that Excel considered the table by default as in ascending order from left to right.

Syntax – HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
lookup_value – It is any text/number/formula/reference cell, you typed or calculated which needs to be find in a row or column. It is compulsory field without which formula results in #N/A error.
table_array – This is a vector or you can say a row or a column in which you want to search the value. It is compulsory field without which formula results in #N/A error.
row_index_num – This is a number of a row starting from top, from which you want to display the value as a result. It is also a compulsory field without which formula results in #VALUE! Error.
[range_lookup] – This is an optional field. It can be used to tell MS Excel that lookup value should be exactly the same or it will be approximate match the lookup value. Value in this position can be “TRUE”/1 for approximate match and “FALSE” / 0 / blank for exact match.

Example – Following example will clear the HLOOKUP function:-

 A B C D E F G H I 1 Count 1 1.9 2.1 4 5 To search 2 1.9 2 Alphabet A B C D E Value in Col B approx. match =VLOOKUP(H1,A1:C6,2) =VLOOKUP(I1,A1:C6,2) 3 Location Delhi Mumbai Calcutta Bangalore Gurgaon Value in Col C approx. match =VLOOKUP(H1,A1:C6,3,1) =VLOOKUP(I1,A1:C6,3,1) 4 Value in Col B exact match =VLOOKUP(H1,A1:C6,2,”FALSE”) =VLOOKUP(I1,A1:C6,2,”FALSE”) 5 Value in Col C exact match =VLOOKUP(H1,A1:C6,3,0) =VLOOKUP(I1,A1:C6,3,0) 6 7

And result you get as under:-
 A B C D E F G H I 1 Count 1 1.9 2.1 4 5 To search 2 1.9 2 Alphabet A B C D E Value in Col B approx. match B B 3 Location Delhi Mumbai Calcutta Bangalore Gurgaon Value in Col C approx. match Mumbai Mumbai 4 Value in Col B exact match #N/A B 5 Value in Col C exact match #N/A Mumbai 6 7

Note:- To get the right result with HLOOKUP you must remember following :-
1.     Top row must be in ascending order from left to right.
2.     It always works from left to right
3.     It stops at the first value that matches the lookup value in case there are duplicate values in the row.
4.     Works only if the lookup value is within the top row of the selected array.

5.     In case exact match you should have written “TRUE” or 0 for range_lookup.