+ Reply to Thread
Results 1 to 4 of 4

INDEX Function to return a value based on multiple criteria.

  1. #1
    Registered User
    Join Date
    03-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    4

    INDEX Function to return a value based on multiple criteria.

    Hello Everyone,
    I'm trying to write and index function that will return a value that meets multiple criteria. I tried to make it work based on some other internet snooping and it's just not working. It uses 2 sheets.

    The function needs to return the value from Column B (sheet1) if, in the given range, column A (sheet1) = C4(sheet2) AND column C(sheet1) = D3(sheet2). This formula will be located on sheet2. The C4 value will change as the formula is applied to additional rows, but the D3 value should remain constant. I've attached a sample doc. My semi-sad efforts trying to do this on my own got me to this: but it doesn't work because I don't really know what I'm doing, hahaha:

    =INDEX('Sheet1'!A3:C14,(MATCH(C4&D3,'Sheet1'!A:A&'Sheet1'!C:C,0)),2)

    Any solution would be incredibly helpful. Feel free to ignore the semi-disaster of my attempt, haha. Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: INDEX Function to return a value based on multiple criteria.

    welcome to the forum, carterbu. try:
    =INDEX(Sheet1!$B$3:$B$14,MATCH(C4&D$3,INDEX(Sheet1!$A$3:$A$14&Sheet1!$C$3:$C$14,),0))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: INDEX Function to return a value based on multiple criteria.

    Try this (remember this array formula, use Ctrl+Shift+Enter):
    Please Login or Register  to view this content.
    Copy down


    Note : the red one are the different from Benishiryo, but the result are same
    Last edited by SDCh; 10-23-2013 at 11:25 AM. Reason: Add Color and Note
    Click (*) if you received helpful response.

    Regards,
    David

  4. #4
    Registered User
    Join Date
    03-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: INDEX Function to return a value based on multiple criteria.

    Thanks so much. Those worked great!

+ 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. SOS:Return multiple values against multi criteria match and index function
    By nitesh_inin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2012, 05:45 PM
  2. Replies: 3
    Last Post: 06-15-2012, 04:19 PM
  3. [SOLVED] Return a value:index/match against multiple criteria on another sheet
    By Southfish in forum Excel General
    Replies: 3
    Last Post: 04-30-2012, 11:08 PM
  4. [SOLVED] INDEX-MATCH function return '0' if search criteria not found
    By Ben.SFM in forum Excel General
    Replies: 2
    Last Post: 04-27-2012, 01:46 PM
  5. Return index value position for value based on two criteria
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-07-2011, 08:03 PM

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