VLOOKUP - I THINK I NEED SOMETHING MORE
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: VLOOKUP - I THINK I NEED SOMETHING MORE

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    LIZ
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    CURRENT FORMULA:
    =IF(ISNA(LOOKUP(B19,$N$16:$S$34)),"",LOOKUP(B19,$N$16:$S$34))

    I NEED TO EXPAND ARRAY TO 60 ROWS. IT WOULD
    BETTER IF I DID NOT HAVE TO SORT FIRST ROW IN NUMBERICAL ORDER. THIS IS FOR A SHIPPING DOCUMENT - WE ENTER INVENTORY ITEM # INTO B19
    I WOULD LIKE TO SORT ARRAY BY PRODUCT DESCRIPTION (COLUMN S)

    I AM LOOKING FOR AN ALTERNATE FORMULA

    I APPRECIATE ANY HELP. THANK YOU IN ADVANCE
    FOR YOUR TIME.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    See MyVlookup at:

    http://www.thewordexpert.com/tipwarez.htm

    It explains the true/false argument that does not require you to sort...
    ~Anne Troy

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try using the false parameter in the vlookup function to avoid sorting your lookup table.

    ex: vlookup(value,table,3,false)


    It's never too late to learn something new.

    Ricky

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    PS: Hawk. Also check out how I use named ranges for my lookup areas. No need to change data range when you use named ranges for entire rows or columns.
    ~Anne Troy

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dreamboat

    Can i suggest posting the answer rather that just pasting a shortcut hyper link to you home page, guys are asking questions and so would be nice if that answer was on this site for us all to see and use.

    And so others can search....
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Hawk

    In addition to the inforamtion already supplied (in particular the False argument for VLOOKUP). Consider using a COUNTIF to test whether the content of B19 is within your data table.

    =IF(COUNTIF($N$16:$N$34,B19)=0,"",LOOKUP(B19,$N$16:$S$34,FALSE))

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com