+ Reply to Thread
Results 1 to 10 of 10

V Look Up.

  1. 04-17-2014, 11:37 AM #1
    Statz
    Statz is offline
    Forum Contributor
    Join Date
    02-26-2010
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    164

    V Look Up.

    I'm using a V look up formula and it's working except one of the rules is preventing me from getting more info. I know the way V Look up works is that when it searched for the information it takes the first cell of information that it finds. In the attachment (yellow highlighted box-I enter a key word Store A) That is a chain which has stores mapped to that chain in the data base.

    When I enter store A, the information that pops up is

    19335 Mart A Address DD


    When I click and drag that down it gives me the same information because it's the first entry that shows up in the data base with that chain.

    What I want is that when I click and drag down it gives me:

    22034 Mart B Address KDJK

    Goal here is that I'll have numerous chains and each time I enter a chain I want all the stores in that chain to populate. This way I can have a 1 pager that just lists the chains I want to see and not have to settle for a long list in the data base.

    I'm using excel 2013.
    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-17-2014, 11:46 AM #2
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    Re: V Look Up.

    On cover sheet, in A8 try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter.)

    Please Login or Register  to view this content.
    and then drag across and down.

    IFERROR will not work if you are using Excel 2003. If that is the case, you can omit the IFERROR from the formula.

    Please Login or Register  to view this content.
    Last edited by sktneer; 04-17-2014 at 11:48 AM.
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
  3. 04-17-2014, 11:49 AM #3
    Russell Dawson
    Russell Dawson is offline
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: V Look Up.

    You need to have a unique name for each Store on the DB page
    If I helped, please don't forget to add to my reputation. (click on the star below the post)

    If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.

    Failure is not falling down but refusing to get up.
    Reply With QuoteRegister To Reply
  4. 04-17-2014, 11:53 AM #4
    Statz
    Statz is offline
    Forum Contributor
    Join Date
    02-26-2010
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    164

    Re: V Look Up.

    Quote Originally Posted by sktneer View Post
    On cover sheet, in A8 try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter.)

    Please Login or Register  to view this content.
    and then drag across and down.

    Thanks that worked. When I click F2 to see the cells included in the formula why does A1 have a box around it?
    Last edited by Statz; 04-17-2014 at 12:00 PM.
    Reply With QuoteRegister To Reply
  5. 04-17-2014, 11:56 AM #5
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,616

    Re: V Look Up.

    Please edit your post to comply with forum rules - namely:

    12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  6. 04-17-2014, 12:01 PM #6
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: V Look Up.

    ROWS(A$1:A1) is in the formula so excel highlights it even tho its not actually referring to info in the cells in column a
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  7. 04-17-2014, 02:51 PM #7
    Russell Dawson
    Russell Dawson is offline
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: V Look Up.

    What? Please be specific. The OP is clear to me and addresses a specific point. What is yours?


    Not you Martin
    Last edited by Russell Dawson; 04-17-2014 at 03:29 PM.
    Reply With QuoteRegister To Reply
  8. 04-17-2014, 03:20 PM #8
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: V Look Up.

    ??????????????
    Reply With QuoteRegister To Reply
  9. 04-17-2014, 11:25 PM #9
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    Re: V Look Up.

    Quote Originally Posted by Statz View Post
    Thanks that worked. When I click F2 to see the cells included in the formula why does A1 have a box around it?
    ROWS(A$1:A1) is used to increment the row number within the SMALL function. i.e. ROWS(A$1:A1) returns 1 and when you drag down the formula to the next row, it becomes ROWS(A$1:A2) which returns 2 and so on. And thus you will get the rows 1,2,3,4.... and so on returned by the formula while you copy the formula down.

    Glad to help you. Thanks for the feedback.
    If that takes care of your question, please mark your thread as solved.
    Reply With QuoteRegister To Reply
  10. 04-18-2014, 03:20 AM #10
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,616

    Re: V Look Up.

    @Russell: me?
    I think I was clear.
    OP cited whole post (05:53 PM),
    I (05:56 PM) made a comment on forum rules - (rule 12).
    OP edited a post at 06:00 PM, deleting part of cited text.
    Thats it
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1