+ Reply to Thread
Results 1 to 5 of 5

HLOOKUP with more than row_index_num

  1. #1
    Registered User
    Join Date
    03-26-2013
    Location
    Mty, Mexico
    MS-Off Ver
    Excel 2003
    Posts
    6

    HLOOKUP with more than row_index_num

    Hi everyone!

    I am having trouble with something like the following example.

    I have a Database with States and Cities. I am doing a "Search" cell with VLOOKUPs that show the most relevant information. I need a formula that shows all the Cities under the same State Search.

    I need a way to put several rows in the HLOOKUP row_index_num part of the formula.

    Hope someone can help.

    Thanks.
    Attached Files Attached Files
    Last edited by CATG; 03-26-2013 at 09:23 PM.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: HLOOKUP with more than row_index_num

    Hi

    Can you please attach an example file so we can get some idea of your structure.

    rylo

  3. #3
    Registered User
    Join Date
    03-26-2013
    Location
    Mty, Mexico
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: HLOOKUP with more than row_index_num

    RYLO,

    I just uploaded an example.

    Thanks.

  4. #4
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: HLOOKUP with more than row_index_num

    in the sample workbook that you uploaded, put this ARRAY formula (see my signature) in cell C12 and drag-fill down until you start seeing "-".

    Please Login or Register  to view this content.
    there is a discrepancy in your data - "Distrito Federal" has a white space character at the end in the CITIES tab, which leads to errors in calculations. hence the use of SEARCH in the formula above; otherwise, i would have preferred to use a simple "=" operator.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: HLOOKUP with more than row_index_num

    Hi

    Firstly, you will have to update your raw data so that the States match the entry you have put in Browse!D2. If you look at the states on the states and cities sheets, you will notice that it has a couple of trailing spaces. These have to be removed.

    Then
    Browse!C12:
    Please Login or Register  to view this content.
    This formula has to be array entered (ctrl, shift, enter). Then copy from C12 down to C30 (to make sure that it has enough range to cover the number of cities).

    HTH

    rylo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1