Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Lookup text & place; skipping blank cells (MS Office 2003)

    Referring to the attached spreadsheet in row 13. I am trying to find a formula which would return the first value in each column. For instance, I would like to get cell B13 to grab the text in B11. Cell C13 should grab from C12, etc. The trick here is that there are 10 potential rows from which to grab a label.

    I've tried using an "If" function with no success [img]/forums/images/smilies/sad.gif[/img] perhaps there's a way to imbed it better?

    thanks
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup text & place; skipping blank cells (MS Office 2003)

    In B13 if you add the formula:
    =VLOOKUP(REPT("z",255),B1:B12,1)

    You can copy it across the row and it will grab the last text value in the column

    Steve

  3. #3
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Lookup text & place; skipping blank cells (MS Office 2003)

    Steve, thank you so much!!! Wow, please explain the formula.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup text & place; skipping blank cells (MS Office 2003)

    REPT("z",255)
    gives a text string of 255 zs:
    "zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz...."

    When you Look this up (with Vlookup) and and "inexact match" excel scans through the list looking for a value greater than that string (which there is none) and when it can't find one, it just returns the last filled text item in the range.

    Steve

Posting Permissions

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