+ Reply to Thread
Results 1 to 7 of 7

A lookup function based on two criteria; one unique & one not unique to the lookup table

  1. #1
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    A lookup function based on two criteria; one unique & one not unique to the lookup table

    Hi,
    I have a problem I'm stuck on. I'm trying to create a lookup formula based on two criteria. The first "category" criteria is a unique value while the second, "characteristic" criteria is not unique to the lookup table/array. I've attached an example sheet but will also include example text below:

    ''''''''''''''''''''''''''''''''''''''
    Category 1
    alpha
    beta
    Category 2
    alpha
    beta
    gamma
    Category 3
    beta
    epsilon
    '''''''''''''''''''''''''''''''''''''
    The lookup I'm trying to make would return, for example: Category 2 beta

    Any insight into this problem is greatly appreciated.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,530

    Re: A lookup function based on two criteria; one unique & one not unique to the lookup tab

    I think you need to explain it further.
    What is the actual setup of your sheet? In the attached sheet, you have shown categories and characteristics in the same column.
    Moreover what is the criteria for your returned result.
    So better you upload a sample workbook with the layout as same as your original workbook along with some sample data in it and also showing the desired output filled manually.
    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.

  3. #3
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    Re: A lookup function based on two criteria; one unique & one not unique to the lookup tab

    Apologies for the lack of clarity on my part.
    The attached file contains the setup for my sheet: the categories and characteristics are in the same column, the characteristics are indented. There are also values assigned to the characteristics. The desired return is the value for a characteristic for a category.
    I've uploaded another spreadsheet reflecting these clarifications and have provided another text example below:
    ''''''''''''''''''''''''''''''''''''''
    Category 1
    alpha .423
    beta .233
    Category 2
    alpha .101
    beta .202
    gamma .305
    Category 3
    beta .456
    epsilon .656
    '''''''''''''''''''''''''''''''''''''

    So an example return for a Category 2 Alpha would be .101

    Sorry again for the confusion,
    Thanks
    Attached Files Attached Files

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,530

    Re: A lookup function based on two criteria; one unique & one not unique to the lookup tab

    Please find the attached sheet to see if this is what you are trying to achieve.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Re: A lookup function based on two criteria; one unique & one not unique to the lookup tab

    Trevasarus, Good evening.

    Your layout looks like a Report Output.
    All the calculus with this layout is more dificult than with Database Layout.

    I separated your column Index in two columns.
    Take a look at my example.

    Is it what you want?
    I hope it helps
    Attached Files Attached Files
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marcílio Lobão

  6. #6
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    Re: A lookup function based on two criteria; one unique & one not unique to the lookup tab

    sktneer & Mazzaropi,

    Thank you both for your help. Both are workable solutions to my problem. Much appreciated.

    Mazzaropi: I'm not sure what a Report Output is Vs a Database Layout but the example you've provided suites my needs.

    Thanks

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,530

    Re: A lookup function based on two criteria; one unique & one not unique to the lookup tab

    You're welcome. Thanks for the feedback and rep.

+ 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] Count unique entries based off unique criteria in another column
    By hambly in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-11-2014, 03:33 PM
  2. [SOLVED] Two Criteria Lookup, with two seporate criteria making a unique identifier.
    By Trevasaurus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-26-2013, 11:00 AM
  3. Replies: 13
    Last Post: 12-19-2012, 02:38 PM
  4. Multiple Criteria lookup + Listing Unique information
    By arekkusu03 in forum Excel General
    Replies: 1
    Last Post: 09-14-2012, 03:27 AM
  5. Lookup Unique based on Multiple Conditions
    By statenja in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-05-2009, 09:31 AM

Tags for this Thread

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