I'm developing a time card with lists. When you pick a job, the corresponding
job number needs to come up. I've tried "If" functions but it seems to be
limited. I can't seem to grasp Hlookup. any help?

Hlookup example below .... where C9 is the cell you want to reference and C2
thru G5 is the range where the info is and 2 represents the number of columns
the lookup is going down.


=HLOOKUP($C9,$C$2:$G$5,2,0)

"Lscrivener" wrote:

I'm developing a time card with lists. When you pick a job, the corresponding
job number needs to come up. I've tried "If" functions but it seems to be
limited. I can't seem to grasp Hlookup. any help?

I can't seem to grasp Hlookup. any help?

The HLOOKUP function is pretty straight forward. Consider the formula:

=HLOOKUP("bb",A2:F4,3,FALSE)

Here, the lookup value is "bb" (case-insensitive, "bb" == "BB" == "bB") and
the lookup range is A2:F5. HLOOKUP scans horizontally across the first row
of the lookup range (A2:F2) until it finds the "bb" value in a cell. Then,
it reads down in that same column to the third row of the lookup table
(including the lookup row, so the 3 indicates 2 full rows down) and returns
that value. For example, using the formula above and the following data in
the range A2:F4:

aa bb cc dd ee ff
11 22 33 44 55 66
111 222 333 444 555 666

HLOOKUP reads horizontally across row 2, which contains "aa", "bb", etc.
When it finds the "bb" entry, it looks in the third row of the lookup table
A2:F4, in the column in which the "bb" was found. Counting downward from row
2 for 3 rows, it finds the value 222.

The lookup table (A2:F4) must contain at least as many rows as is specified
in the HLOOKUP function. It can certainly contain more rows (e.g., A2:F100
is perfectly valid) but it cannot contains fewer rows (e.g., A2:F3 is not
valid). If you have too few rows specified in the function, you'll get a
#REF error.

If the lookup value (in the example, "bb") is not found in the first row of
the lookup table, the function returns #N/A.

In the HLOOKUP function, the final parameter FALSE tells HLOOKUP to look for
an exact match. If FALSE is specified, the elements in the first row need
not be in sorted or any other order, and only an exact match is consider a
success. If the final parameter is TRUE or is omitted, HLOOKUP finds the
closest match that does not exceed the lookup value. In this case, the first
row of the lookup table must be in sorted order. If HLOOKUP is used for a
closest match (final parameter is TRUE) and the first row of the lookup
table is not in sorted ascending order, the results are unpredictable.
HLOOKUP may return a #N/A error or it may return a value, but that value is
likely not what you expect. If the data isn't sorted, it's a crapshoot.

You do not need to specify the lookup value directly in the formula. You can
use a cell reference (no quotes) to use the contents of another cell. For
example, the formula

=HLOOKUP(K10,A2:F4,3,FALSE)

does the same thing as the earlier function, but uses the contents of cell
K10 as the lookup value. As K10 changes, the result of the HLOOKUP function
also changes.

The VLOOKUP function works just like the HLOOKUP function, but instead of
scanning across the first row looking for a match and then moving down,
VLOOKUP scans down the first column of the lookup range for a match and then
moves to the right to get the return value.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"Lscrivener" wrote in message
...
I'm developing a time card with lists. When you pick a job, the
corresponding
job number needs to come up. I've tried "If" functions but it seems to be
limited. I can't seem to grasp Hlookup. any help?