HLOOKUP checks the first row of the given data table for the specified value and returns a corresponding value in that column, based on the row given.
Syntax
lookup_value: The value to look for in a range/array. E.g. "B"
data_table: The table containing the data to test. E.g. A1:G2
row_index: The row that you want to get the corresponding value from (starting with 1 for the top-most row in the data table). E.g. 2
mode: Specifies how the data_table must be,
-1 - The first row must be sorted in descending order. The exact match or the next greatest value is considered.
0 - The first row can be unsorted. The exact match or regex match is considered.
1 and >2 - The first row must be sorted in ascending order. The exact match or the next smallest value is considered.
Remarks
Use named ranges for data_table . This helps refer a single source data table to create multiple tables for calculation and analysis.
If you don't remember the complete text you have to search for, use .* , .? and / instead.
.* - Can be used to replace any number of characters. E.g.
Zyl.*
finds
Zylker
.? - Can be used to replace a single character. E.g.
Karthi.?
finds
Karthik
and
Karthic
/ - Can be used to include *, ? and / in the search term. E.g. Zylker/* finds Zylker* and Karthi.?/? finds Karthik? and Karthic?
HLOOKUP is case-insensitive i.e. "APPLES" and "Apples" are treated similar.
Examples
Formula |
Result |
=HLOOKUP( "B" ; A1:G2 ;2;0) |
70 |
=HLOOKUP(2508; I1:O2 ;2;1) |
Ming Yin |
Possible Errors
Errors |
Meaning |
#N/A! |
|
#NAME! |
|
#VALUE! |
|
#REF! |
|
Similar Functions
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
Write to us: support@zohoforms.com