HLOOKUP
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
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
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.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
Zoho Sheet Resources
Zoho Forms Resources
Zoho Sign Resources
Sign, Paperless!
Zoho TeamInbox Resources
Zoho DataPrep Resources
Design. Discuss. Deliver.
Related Articles
XLOOKUP
Tests the search_table for the given lookup_value and returns a corresponding value from the result_table. XLOOKUP is an advanced version of the VLOOKUP and HLOOKUP functions that searches both vertically and horizontally. Syntax ...MATCH
Returns the relative position of the given value in a given range. Syntax MATCH(search_item; search_region; [match_type]) search_item: The value to search for in the given range. E.g. "Ming Yin" search_region: The row/column to search. E.g. C2:C7 ...LOOKUP
Searches for a given value in a sorted row/column and returns a corresponding value from the given row/column. If more than one exact match appears, LOOKUP takes the value in a random order. If no exact match appears, LOOKUP takes the next smallest ...VLOOKUP
VLOOKUP checks the first column of the given data table for the specified value and returns a corresponding value, in that row, based on the column given. Syntax VLOOKUP( lookup_value; data_table; column_index; [mode] ) lookup_value : The value ...