Explanation Of ROW() In Formula/Function

    Hi


    I have a very urgent query...(for my boss! eek!)


    I have a formula in a spreadsheet set up by someone else and it says..


    =HLOOKUP(C$30,J$2:$N$259,ROW()-ROW($2:$2)+1,)


    Can some one explain what it is doing in the bolded part?


    I understand basic vlookup and hlookups but not what this is doing. I have looked at the help but it only says that it returns the row numbers as reference but what does that mean..?


    - and what does the "+1," and the comma at end mean.


    Any help really, really appreciated..


    dugong

    Re: Hlookup Formula - Explanation?


    Hi


    it would appear that the element of the formula in bold is there to calculate the row number to be accessed in the reference range; in this instance 29. The seemingly superfluous comma would usually be followed by either "true" or "false" ( or 0 or 1), depending upon whether an exact match was required or the nearest match, in this instance it would default to 0


    HTH


    Robert

    Re: Hlookup Formula - Explanation?


    The bold portion is the row reference being looked up, as per Excel Help:


    Row_index_num is the row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on...


    The ROW command returns a row number...ROW() returns the current row, ie if the formula was in cell A5 it would return 5; ROW($2:$2) returns 2.


    So if your HLOOKUP is looking for the value in the lookup range on the same row as your HLOOKUP formula.


    The comma at the end is superfluous and just means the function will look for the nearest apoproximate match.


    HTH


    Just next to your Esc key is the F1 key...;-)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login