+ Reply to Thread
Results 1 to 7 of 7

Index/Match Formula with nested error test that has an error that is unsolvable!

  1. #1
    Registered User
    Join Date
    09-10-2015
    Location
    Caringbah, NSW, Australia
    MS-Off Ver
    Office 365
    Posts
    43

    Index/Match Formula with nested error test that has an error that is unsolvable!

    This has to be the weirdest problem I have ever encountered.
    I have a workbook with multiple worksheets.
    The master (data source)worksheet is called Combined.
    Two of the other worksheets (e.g. Macquarie Bank - Personal, NAB Credit Card) draw data from the Combined worksheet.

    I have attached an Excel workbook for perusal.

    On the NAB Credit Card worksheet the formula works perfectly and I can fill it up or down without errors!

    This formula works correctly on all sheets EXCEPT Macquarie Bank – Personal:

    =IF(OR(ISBLANK(VLOOKUP($B20,Combined!$B$20:$C$3019,1)),VLOOKUP($B20,Combined!$B$20:$C$3019,1)<=0,$B20<>VLOOKUP($B20,Combined!$B$20:$C$3019,1)),"",INDEX(Combined!$D$20:$D$3019,MATCH('NAB Credit Card'!$B20,Combined!$B$20:$B$3019,0)))

    The nested error checking before the actual index/match is to prevent #NA and #Value errrors from being displayed and so preventing correct data transfer and resulting calculations on the NAB Credit Card worksheet.

    HOWEVER, if I either copy and edit the formula, or type it is manually, to the Macquarie Bank - Personal worksheet it fails!

    THIS is the formula being used on the worksheet Macquarie Bank – Personal and is NOT working correctly as it does on all other worksheets:

    =IF(OR(ISBLANK(VLOOKUP($B20,Combined!$B$20:$C$3019,1)),VLOOKUP($B20,Combined!$B$20:$C$3019,1)<=0,$B20<>VLOOKUP($B20,Combined!$B$20:$C$3019,1)),"",INDEX(Combined!$D$20:$D$3019,MATCH('Macquarie Bank - Personal'!$B20,Combined!$B$20:$B$3019,0)))

    Any ideas as to why this is happening? The ONLY difference in the formula is in the MATCH portion of the formual where the reference to the source changes.

    Any ideas of how to fix it?

    A SECOND issue, visible in the attached workbook, worksheet NAB Credit card occurs in cells L521 and L624 which is affecting the desired output in cell M624. All of the values are correct and cell L521 total matches cell L624 total som M624 should be outputting "OK", not "Error"! Any idea as to what is happening in this formula?

    Thanks in advance for any assistance.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Index/Match Formula with nested error test that has an error that is unsolvable!

    A SECOND issue, visible in the attached workbook, worksheet NAB Credit card occurs in cells L521 and L624 which is affecting the desired output in cell M624.
    Neither L521 nor L624 contains any data at all on that sheet ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Index/Match Formula with nested error test that has an error that is unsolvable!

    AliGW,

    It's in 'NAB Credit Card' sheet.


    Regards.

    Ps. It's will be helped if the op tell us what he want from the formula.
    I can't understand a very long descriptions. T_T

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Index/Match Formula with nested error test that has an error that is unsolvable!

    Ps. It's will be helped if the op tell us what he want from the formula.
    The OP has been a member here long enough to know this.

    If I open a workbook as busy as this one and can't immediately find (a) the cells in question and (b) notes telling me what the reuslts should be, then I pass on to the next query.

  5. #5
    Registered User
    Join Date
    09-10-2015
    Location
    Caringbah, NSW, Australia
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Index/Match Formula with nested error test that has an error that is unsolvable!

    Firstly, I thought I gave a good description of what I wanted without going into specific details but, as this is what you need:

    I am trying to extract data from the Combined worksheet which has ALL transactikons in the year, in multiple accounts, into worksheets relatred to specific accounts - e.g. NASB Credit Card or Macquarie Bank - Personal, using the exact same coulm headings as exist on the Combined sheet. This is to allow easier analysis of account specifi transactions instead of searching the entire, and lengthy Combi9ned worksheet!

    Does that explain it?

    The formula is long in essence because I have prefaced the actual index/match part of the formula with error checking formul;ae/functions to avoid the index/match returning #NA or #Value error when the cell on, for exanmple NAB Credi Card worksheet shoul be showing no data if the data list is exhausted in the Combined worksheet (because the NAB Credit Card worksheet has been set up with many additional rows for data as a template for use in any year and the number of rows has been created to include any expected number of entries that match the Combined worksheet in a given year.

    I am trying to avoid a lengthy process prone to error by inexperienced users of copy/paste where rows from combined, in future years, may exceed any given number of rows provided on the NAB Credirt Card worksheet (or others) where a copy/paste from Combined may unwittingly overwritre totals row and summary area beneath totals.

    Clear enough?

    With regards to to comment that there is no data in the cells on NAB Credit Card worksheet, I would ask that you look again because there is DEFINITELY data there!

  6. #6
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Index/Match Formula with nested error test that has an error that is unsolvable!

    Not sure what you're really want, please try....

    in your formula

    =IF(OR(ISBLANK(VLOOKUP($B20,Combined!$B$20:$C$3019,1)),VLOOKUP($B20,Combined!$B$20:$C$3019,1)<=0,$B20<>VLOOKUP($B20,Combined!$B$20:$C$3019,1)),"",INDEX(Combined!$D$20:$D$3019,MATCH('NAB Credit Card'!$B20,Combined!$B$20:$B$3019,0)))

    seem you use VLOOKUP as a default no.4 options , so
    VLOOKUP($B20,Combined!$B$20:$C$3019,1) is the same to VLOOKUP($B20,Combined!$B$20:$C$3019,1,true)

    please try to change to VLOOKUP($B20,Combined!$B$20:$C$3019,1,false)

    Regards.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Index/Match Formula with nested error test that has an error that is unsolvable!

    Clear enough?
    It would be MUCH CLEARER if you would (a) signpost the workbook and (b) show in the workbook the results you are expecting in an adjacent cell (manually calculated). Many people prefer visuals to long text explanations - and that's not just members whose first language is not English!

    With regards to to comment that there is no data in the cells on NAB Credit Card worksheet, I would ask that you look again because there is DEFINITELY data there!
    Indeed it is - I thought I was looking at the correct worksheet, but evidently not!

+ 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. Error in my INDEX MATCH Formula?
    By gimlay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2019, 02:05 PM
  2. [SOLVED] Index Match Formula Error
    By DontExcelAtMuch in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2013, 06:43 PM
  3. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  4. Nested Index Match Error
    By cyclops755 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-13-2012, 01:26 AM
  5. [SOLVED] Intermittant #N/A error in nested vlookup index formula that shouldn't occur
    By DPKologie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-01-2012, 04:25 PM
  6. #N/A Error with INDEX MATCH formula
    By paperwings25 in forum Excel General
    Replies: 5
    Last Post: 04-04-2012, 02:36 PM
  7. Index & Match (Formula Error)
    By rickyilas in forum Excel General
    Replies: 2
    Last Post: 02-26-2010, 11:10 AM

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