Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    LOOKUP ISSUE (EXCEL 2000)

    My spread sheet has numerous (over 100) groups of items. Using the VLookup function I can search and get the corresponding PRICE for each item. I can not however get the PRICE BREAK QTY'S, which are listed at the top of each group.
    Any help with this would be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOKUP ISSUE (EXCEL 2000)

    Welcome to Woody's Lounge!

    An array formula specialist can probably come up with a formula-only solution. I created a small custom function:

    Function LookupTitle(SearchVal, Table, ColIndex)
    ' Returns the "title" for SearchVal in column ColIndex in Table
    ' There should be a blank row above each title row.
    Dim lngRow As Long
    ' Determine row in which search value is found.
    lngRow = Application.WorksheetFunction.Match(SearchVal, Table.Columns(1), 0)
    ' Go to column, then move up to before blank cell.
    LookupTitle = Table.Cells(lngRow, ColIndex).End(xlUp)
    End Function

    I then used this function in formulas to look up the price break qtys, for example in cell C4:

    =LookupTitle($B$2,$B$7:$F$24,2)

    See attached modified workbook. Note: you should not have macro security set to High, that will prevent VBA code from running. Medium is the recommended setting.

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: LOOKUP ISSUE (EXCEL 2000)

    Hi Bobby & welcome

    I have managed to achieve it using an array formula. For it to work each group of items must have a blank row above it, including the first group.

    Here is an example formula:
    <code>=INDEX($B$7:$F$24,MAX(IF(ROW($B$7:$B$24)>MAT CH($B$2,$B$7:$B$24,0)+6,0,ROW($B$7:$B$24)*ISBLANK( $B$7:$B$24)-5)),2)</code>


    The range references must include the blank row above the first group of data. About half way along the formula it adds 6 and near the end it subtract 5. These values are based on the row number for the first row (the blank row) of the lookup table. In this case as the first row is row number 7, you add row number-1 and subtract row number-2. The 2 at the end is the column number it needs to look up to get the result (the same column number that the VLOOKUP formulas use).

    The principle behind this formula is to calculate the row number for the blank rows as the headings will be on the next rows.

    It is a rather complex array formula and the VBA method used by Hans is much easier to use and understand. If you do not mind using VBA then go with that method.

  4. #4
    New Lounger
    Join Date
    Oct 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOKUP ISSUE (EXCEL 2000)

    Thanks HansV and Tony55.
    Both worked great. This is a reaccurring problem with Excel files I get from quite a few sources. Thanks a bunch.

    Bobby [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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