+ Reply to Thread
Results 1 to 4 of 4

lookup multiple instances

  1. #1
    Registered User
    Join Date
    11-22-2010
    Location
    Joplin, MO
    MS-Off Ver
    Excel 207
    Posts
    23

    lookup multiple instances

    I am using VLOOKUP to return the addresses for my bottom stores to basically take out sorting as well as do a few other things, my problem comes if there are multiple instances of the same #. Here is a small example:


    $10 ROGERS
    $11 HARRISON
    $16 COMMERCE
    $25 SILOAM SPRINGS
    $10 CONWAY
    $17 SHERWOOD

    To return the stores I want I am using the following formula =VLOOKUP(SMALL(Sheet2!J:J,ROW(C1)),city,2,FALSE) which is all fine and dandy for the worst store in this case Rogers, but instead of listing Conway as my second worst is lists Rogers again. any ideas?

    Thanks!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: lookup multiple instances

    Can you explain why ROGERS store is the worst and not CONWAY?
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    11-22-2010
    Location
    Joplin, MO
    MS-Off Ver
    Excel 207
    Posts
    23

    Re: lookup multiple instances

    I guess I need to show everything that is going on for this to make sense. Attached is my spreadsheet with numbers changed. I want to be able to adjust Minimum sales and max distance to see all my top and bottom stores within a given area. Attached is what I have built so far with a lot of the data removed to manage the size. store info list.zip

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: lookup multiple instances

    I didn't look at your file (it's way toooooooo big!).

    See if this helps...

    How to generate a bottom N list:

    https://www.excelforum.com/showthread.php?p=3772120
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Lookup formula to add value of multiple instances
    By Juanmsz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2013, 09:21 AM
  2. [SOLVED] Lookup for multiple instances
    By sonyaswan in forum Excel General
    Replies: 2
    Last Post: 07-17-2012, 08:26 AM
  3. lookup for multiple instances across various sheets?
    By finance14 in forum Excel General
    Replies: 2
    Last Post: 06-29-2011, 12:30 AM
  4. lookup multiple instances
    By NathanR in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2007, 07:03 PM
  5. Lookup function for a value that has multiple instances
    By Rafat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2006, 04:36 PM

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