+ Reply to Thread
Results 1 to 8 of 8

IF MATCH Function

  1. 04-10-2013, 02:42 PM #1
    n1kk1
    n1kk1 is offline
    Registered User
    Join Date
    06-07-2006
    Posts
    57

    IF MATCH Function

    Hello, I have attached a spreadsheet which includes a formula which has previously worked but, for some reason, has stopped.

    In worksheet ;invoice publishing' I need to return a value in A17 depending on the code in L10. I need it to look at the information in PRINT Orders worksheet.

    I have all the formulas entered, it looks like this

    =IF(ROWS($A$72:$A72)>$A$15,"",INDEX('PRINT orders'!D6:D95,MATCH(ROWS($A$72:$A72),'PRINT orders'!AK6:AK44,0)))

    If anybody could take a look at the attached and give me an indication as to why this has stopped working that would be much appreciated!

    Thanks
    Nikki
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-10-2013, 03:59 PM #2
    Oppressed1
    Oppressed1 is offline
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: IF MATCH Function

    Your problem is not there, it's the COUNTIF. Have it search A, not B

    =IF(A6='Invoice Publishing'!$L$11,COUNTIF(B$5:B6,'Invoice Publishing'!$L$11),"")
    =IF($A6='Invoice Publishing'!$L$11,COUNTIF($A$5:$A6,'Invoice Publishing'!$L$11),"")
    Chip, if you can read this, you're STILL making the world a better place with your immeasurably valuable gifts. Every week, you continue to help me (and untold numbers of others) tremendously. Practically no alternative outlets can match your effectiveness, even over the vast number of topics you demystify and empower usage of. You were, and still are, amazing.
    Reply With QuoteRegister To Reply
  3. 04-11-2013, 05:01 AM #3
    n1kk1
    n1kk1 is offline
    Registered User
    Join Date
    06-07-2006
    Posts
    57

    Re: IF MATCH Function

    Thank you, that works on every sheet apart from this one! It is exactly the same as far as I can see..
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-11-2013, 05:17 AM #4
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: IF MATCH Function

    Apologize in advance if i don't get your goal correctly, but may i suggest this ARRAY formula, in A21?

    =INDEX('ART Sales'!C$8:C$1000,SMALL(IF('ART Sales'!$A$8:$A$1000='Invoice - Original Art'!$I$14,ROW('ART Sales'!C$8:C$1000)-7),ROW('ART Sales'!C1)))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  5. 04-11-2013, 08:36 AM #5
    n1kk1
    n1kk1 is offline
    Registered User
    Join Date
    06-07-2006
    Posts
    57

    Re: IF MATCH Function

    Thanks for the reply, I'm afraid this doesn't work. It changes the data in column A of Art Sales for some reason and it only returns the first line not every instance. The original formula I have has always worked and I use it on other sheets, it just isn't responding on this sheet for some reason...
    Reply With QuoteRegister To Reply
  6. 04-11-2013, 08:40 AM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: IF MATCH Function

    It's return only first instance, because you don't confirm this formula as an ARRAY formula.

    See in my signature how to confirm an ARRAY formula.
    Reply With QuoteRegister To Reply
  7. 04-11-2013, 09:06 AM #7
    n1kk1
    n1kk1 is offline
    Registered User
    Join Date
    06-07-2006
    Posts
    57

    Re: IF MATCH Function

    This aside, it also changes column A in the 1st worksheet.

    I would appreciate assistance with my initial formula if anyone can help?

    Thanks
    Reply With QuoteRegister To Reply
  8. 04-11-2013, 09:25 AM #8
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: IF MATCH Function

    This aside, it also changes column A in the 1st worksheet.
    There is no formula that can change the values in another sheet.

    ...I would appreciate assistance with my initial formula if anyone can help?
    Good luck with this.
    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)

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