hlookup - looking up the content of a CELL

David,

Welcome to the Numbers discussion. Your use of the HLOOKUP function appears to be an unusual one. Normally the "Rows Range" parameter would include a range of columns. Where you have A4:A8, I would expect something like A4:D8 and I would expect you to be looking for a match in the range A4:D4 in this case. The way you are using HLOOKUP with just a single column, you will either get bake the content of A5 (the second row in the 1-column range), or you will get an error, depending upon whether you get a match in A4. This could more easily and clearly been done with an IF statement.

Or perhaps I have misunderstood you.

Jerry

I will add that using the function exactly as you have described works perfectly well here, so it's not a systemic problem with Numbers.

Jerry

mac-a-rooney wrote:
actually i think you might be wrong. because the formula you are describing is the VLOOKUP formular, not Hlookup as soon as I know.

David,

As anyone who watches this discussion has witnessed, I can and do make mistakes, but when I use any formula more complicated than an IF statement, I always look it up and refer to the Function Browser for syntax, as I did in this case. Click the Function icon on the Toolbar and choose View Function Browser. Here's a screen shot of your solution:



My expression is: =HLOOKUP(A3,T1 :: $A$4:$A$8, 2,FALSE) as shown in the screen shot. Remember that your system language settings require semicolons where I have commas.

Jerry

David,

Your screen shot links are not working. To post here from PhotoBucket, mouse over the image that you want to link to and click on the field below the image that is called HTML Code. Copy that text string and Paste it here in the forum.

You wrote:

I guess you example shall show pretty obvious that the formula in cell B3 does not work because the content of A3 (Dog) can not be found on the H2O Sheet Table T1.

That is proof that the formula does work. The function is supposed to return an error condition when the search is not satisfied. It is not the function's fault that the value you searched for is not present in the target range.

I look forward to seeing your screen shots and trying to understand the problem better.

Jerry

David,

OK, we're making progress now. Your current formula has two problems that I can see.

First, the Function is not being recognized by the compiler because it isn't spelled correctly for the localization you are presently set to. I can tell this because the function name would have been converted to all caps if it had been recognized.

Second, in your screen shots you have two Sheets with a reference from one to the other. We we do this we must include the sheet name in the reference. The proper reference in this case would be: "Blatt 2 :: Addresses :: B".

Let's make those two changes and see what happens.

Jerry

mac-a-rooney wrote:
Actually I did not really get what you said here:

"because the function name would have been converted to all caps if it had been recognized."

caps=captures? but what are the captures? is it just the reference how it appears in the formula syntax?

David,

We are speaking about font case. Caps is short for Capital Letters, or Upper Case, as opposed to Lower Case. The "C" in "Caps" is upper case and we say that the word is "capitalized". "CAPS" is an example of "all caps". Numbers converts the function name to "All Caps" when it is recognized by the compiler, so in the English mode "Hlookup" will become "HLOOKUP".

Jerry

My own tip:

always type the function names in lowercase.
So, if there is a typo we are warned of it : the name remains in lowercase.
If the spelling is correct the name automatically switch in UPPERCASE.

In your screenshot, if hlookup appeared in lowercase, I guess that it's because your system is set to use Deutsch. If I am right hlookup needed to be spelled : wverweis. With this spelling, it would be displayed as : WVERWEIS .

Remember that on the forum, formulas are given for a system running with English settings:
English wording, period as decimal delimiter, comma as parameters separator.

You system settings seems to be :
Deutsch spelling, comma as decimal delimiter, semi colon as parameters separator.

Yvan KOENIG (VALLAURIS, France) vendredi 25 juin 2010 11:58:31

mac-a-rooney wrote:
it means then, that the function, i.e. HLOOKUP, VLOOKUP, etc., has to be written in capital letters, as an indicator for the user, to have a chance that the function will work at all. but this condition of "all caps" only holds for the funktion's name (hlookup, sum, ...) not for the arguments within it. so by referring to a table which is named and written in lower case letters, say addresses, then this is also how it appears as an argument within the formular.

No. It means the formula parser will automatically change a function name to ALLCAPS if it recognizes the function, which, as Yvan points out, may be used by the user as an check for typos. Cell addresses typed as "b4" will also be changed to "B4", but only when the formula is confirmed by pressing enter/return, tab, or the Accept button.

Cells references including a Name are case sensitive. "Table 1" is not the same as "table 1" (or as "TABLE 1" and will not be recognized unless the spelling matches in case as well as by letters.

Therefore, and that might be crucial for all other observers of this thread, one is really urged to carefully watch out to select the TABLE rather than just the Sheet when selecting the search matrix!
...and pls correct me if I am wrong...

The user must specify enough information to uniquely identify the search location. For cell B2, on the same table as the cell referencing it, "B2" is sufficient.

For B2 on a table other than the table of the cell referencing it, the Table Name and "B2" are usually necessary.

For B2 on a table on a sheet other than the sheet containing the table of the referring cell, the Sheet Name, Table name and "B2" are usually necessary

The cell reference may be typed into the formula bar, or may be entered by clicking on the cell itself.

Examples:



From top to bottom:

1 The error message is a bit misleading. The error is in the sheet name (lower case s) and the table name (lower case t).

2. Formula created by:
Type =
Click Sheet 1, then Click B3 on Table 1 (of Sheet 1).
Click Accept (green check)

3. Formula created by typing into Formula Bar. Note lower case b in "b2".

4. Result after clicking Accept (green check) in 3.

Regards,
Barry