+ Reply to Thread
Results 1 to 8 of 8

I need some help with an Index and Match function

  1. 04-29-2020, 11:59 AM #1
    deniseb27
    deniseb27 is offline
    Registered User
    Join Date
    02-14-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    27

    I need some help with an Index and Match function

    Hi everyone,

    I'd previously asked for help with this issue and it was solved, but I have new requirements and I can't get the formula to work in this new context.

    Attached is two worksheets. On Sheet 1, I want to populate Column G with info from the table on Sheet 2. Cell G7 should read 0 because the project is Small and in Stage 1 (Columns A and C on Sheet 1, and cross referencing with the table on Sheet 2. Also, I usually do an "if error" and "" to give blanks, but I'm not sure how to do that in an array formula. So can someone help with the Index and Match function and, if possible, an "iferror" to display blanks when there's no data?

    Thanks so much!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-29-2020, 12:07 PM #2
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,739

    Re: I need some help with an Index and Match function

    does this work
    =IFERROR(INDEX(Sheet2!C:C,MATCH('Sheet 1'!C6&'Sheet 1'!A6,Sheet2!A:A&Sheet2!B:B,0)),"")
    Not an array
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 04-29-2020, 04:34 PM #3
    deniseb27
    deniseb27 is offline
    Registered User
    Join Date
    02-14-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: I need some help with an Index and Match function

    It doesn't seem to. I've tried changing some of the details (i.e. changing a project size from Small to Large) but it doesn't update. Thanks for trying!
    Reply With QuoteRegister To Reply
  4. 04-29-2020, 04:42 PM #4
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,739

    Re: I need some help with an Index and Match function

    seems to work ok, when i change things around

    what exactly is not working , i have change the size and the value and returns OK
    can you perhaps give some specific examples and reference

    see my attached where i have made a few changes in sheet2 and sheet1 updates
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 04-29-2020, 04:53 PM #5
    deniseb27
    deniseb27 is offline
    Registered User
    Join Date
    02-14-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: I need some help with an Index and Match function

    This one worked, thank you! What was the difference between the two?
    Reply With QuoteRegister To Reply
  6. 04-29-2020, 04:56 PM #6
    Limor_OP
    Limor_OP is offline
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: I need some help with an Index and Match function

    It works OK, you should press CTL+SHIFT+ENTER as it is an array formula.
    Reply With QuoteRegister To Reply
  7. 04-29-2020, 05:06 PM #7
    deniseb27
    deniseb27 is offline
    Registered User
    Join Date
    02-14-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: I need some help with an Index and Match function

    Thank you!
    Reply With QuoteRegister To Reply
  8. 04-29-2020, 05:08 PM #8
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,739

    Re: I need some help with an Index and Match function

    i'm on 365 office - and i think array formulas don't need the control +shift+enter to work
    I didnt do anything different
    BUT that may be the confusion and apologies about that
    i need to be careful about array & non Array
    updated for non array
    https://www.quora.com/EXCEL-QUESTION...in-columns-A-B
    =IFERROR(INDEX(Sheet2!C:C,MATCH('Sheet 1'!C6&'Sheet 1'!A6,index(Sheet2!A:A&Sheet2!B:B,,),0)),"")
    Attached Files Attached Files
    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)

Similar Threads

  1. Using index match function instead of VLOOKUP to match two criteria in another worksheet
    By PHHack in forum Excel General
    Replies: 7
    Last Post: 10-03-2019, 11:23 AM
  2. [SOLVED] Index/Match/Min/ABS Function needs to ignore one value in the index.
    By pronghorn in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-16-2019, 02:04 AM
  3. How to apply an Index.Match.Match function to all entries in a Listbox on a User Form
    By jason.drozd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-25-2018, 01:54 AM
  4. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  5. Problems applying INDEX-MATCH-MATCH function on other data
    By LennartB in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-13-2015, 05:33 AM
  6. [SOLVED] Stuck on Match function with #N/A; attempting to reverse Index/Match
    By Cappytano in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-10-2014, 06:39 PM
  7. Lookup or Index(match) function not in ascending order and not an exact match
    By Tobias1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2013, 12:37 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1