Resources and Links Page for Curtis Frye, That Excel Guy

Title Graphic for That Excel Guy Index Page

Curt Frye That Excel Guy Link to Home Page Curt Frye That Excel Guy Writing Page Curt Frye That Excel Guy Speaking Page Link Curt Frye That Excel Consulting Page Link Curt Frye That Excel Guy Resources Link Curt Frye That Excel Guy Resume Link Curt Frye That Excel Guy Contact Link

     

    I received an e-mail from an Excel Annoyances reader in the Netherlands who indicated that he'd run into difficulties using an INDEX formula I provided in the American edition of the book. Specifically, he had to use a semicolon instead of a comma to delimit the formula's arguments and asked that I correct the issue in subsequent printings. As it turns out, both the reader and the text are correct, but whether you use commas or semicolons depends on your Windows language settings.

    The INDEX formula is part of the broader LOOKUP family, which includes VLOOKUP and HLOOKUP. You can type decimal values directly into a VLOOKUP formula. In most European countries, you would write the decimal value for "three and a half" as 3,5 (as opposed to 3.5 in English-speaking countries), so using a comma as a decimal separator would change the formula =VLOOKUP(1.5,A2:C10,2), which has the correct number of arguments (three), to =VLOOKUP(1,5,A2:C10,2), which has an incorrect number of arguments (four).

    If your computer's default language setting uses commas to indicate decimal values,  you might encounter problems using the INDEX formula described on page 84. The trick is to change the commas to semicolons. In English, an INDEX formula should be written using the syntax INDEX(reference, row_num, column_num, area_num). If your system uses a non-English European language as its default, or another language that uses a comma as the decimal separator, try the syntax INDEX(reference; row_num; column_num; area_num). In the above example, the formula would read to =VLOOKUP(1,5;A2:C10;2).