+ Reply to Thread
Results 1 to 8 of 8

IF MATCH Function

  1. #1
    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

  2. #2
    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.

  3. #3
    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

  4. #4
    Forum Moderator 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.

  5. #5
    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...

  6. #6
    Forum Moderator 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.

  7. #7
    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

  8. #8
    Forum Moderator 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 to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1