+ Reply to Thread
Results 1 to 2 of 2

Excel Formula: IFERROR, INDEX, SMALL, MATCH

  1. #1
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    1

    why does my IFERROR, INDEX, SMALL, MATCH not work :(

    Hi, I'm new to this forum and would like to ask for help on an excel formula. I currently have an excel (example attached) where I have employees name in the first column and job functions in the first row across the top. Next to each employee name, I have entered if they perform that job function, indicated by "Yes". What I would like for the formula to do is reference an employee's name (cell B25 of attached example) and below, list all job functions where "Yes" is indicated. For example: Cornelius Johns (cell B25) would display "Sales" in cell B26.

    I have attempted to use an array =IFERROR(INDEX(,SMALL(IF(INDEX(,,MATCH())="Yes" formula, however have been unsuccessful.

    Any advice is greatly appreciated
    Thank you in advance
    Attached Files Attached Files
    Last edited by FDibbins; 05-12-2014 at 10:29 PM.

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

    Re: Excel Formula: IFERROR, INDEX, SMALL, MATCH

    Try this...

    This array formula** entered in B26:

    =IFERROR(INDEX($B$2:$H$2,SMALL(IF(INDEX($B$3:$H$17,MATCH(B$25,$A$3:$A$17,0),0)="Yes",COLUMN($B$3:$H$17)-COLUMN($B$3)+1),ROWS(B$26:B26))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across to E26 then down until you get a row full of blanks.
    Last edited by Tony Valko; 05-12-2014 at 10:17 PM. Reason: Changed E25 to E26
    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. Help to simplify an =IF(IFERROR(INDEX(...,MATCH( formula
    By sa12345 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2014, 03:40 AM
  2. [SOLVED] IFERROR+INDEX+MATCH Formula is not returning the correct value
    By bxk006 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-16-2013, 09:50 AM
  3. Wrong result displaying using IFERROR-INDEX-MATCH-ROW Formula
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2013, 01:03 PM
  4. comparision between two excel sheet using VBA Code for Index/match/and/iferror formula
    By gaurangaero in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2012, 10:08 AM
  5. Looking for better formula of iferror, index and match
    By DavidRoger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-23-2012, 07:50 AM

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