from Pierre Leclerc at http://www.excel-vba.com
Excel-lence.net - by Affordable Solutions
What
can be done with HLOOKUP |
There
are 3 functions in the LOOKUP family: |
The HLOOKUP function in ExcelHLOOKUP allows to look for a value in a row based on a certain value in another row. So you look up for "John" in row 1 and you get his address in row 2. HLOOKUP (working with rows) IMPORTANT NOTE: The values within which you are looking up MUST BE IN ASCENDING ORDER (1, 2, 3, 4... or a, b, c, d). This is one of the reasons I switched to the magic function and INDEX/MATCH. Here is a basic HLOOKUP formula: =HLOOKUP(2,A1:G32,4, FALSE) The syntax for these formulas is as
follow, the first argument (2) is what you are looking for, the second
argument (A1:G32) is the range you are looking up within, the third
argument (4) is the row from which the answer is extracted, and the fifth
argument (FALSE) tells Excel that you are looking for an EXACT match
and not the next lower value. If you omit the fifth argument or use TRUE
the formula will return the answer for the next lower value if it doesn't
find EXACTLY what you are looking for. For example if you are looking up
for Peter and the formula only finds Albert and Suzan it will return the
answer for Albert. If you are looking up for 32 and Excel finds only 24
and 56 the formula will return the answer for 24.: You can also use the address of a cell in
which you change the value as first argument in the formula (ex:
J20) Remember to ALWAYS use "FALSE" as fifth argument. HLOOKUP is always
looking up within the first row of the table that you submit as second
argument. If you want the third row to be the look up column you need to
change the second argument to a3:G32 and the third argument to
2. A TIP: Why not use K2000 instead of G32 so that you can add information in your table (rows and columns of data) (new clients, 2nd phone number, email address, etc...) without having to change all the formulas that refer to the table. |