+ Reply to Thread
Results 1 to 32 of 32

Index, match, len

  1. #1
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Index, match, len

    Good afternoon,

    My name is Nadine and I am looking for help on trying to solve get this formula to work or you have a better way to help me get what I need, that would be appreciate!

    I have two workbooks, one being larges and have several rows and columns. One column has Claim numbers in it (there are several rows before that column) and one is a blank column that I need to have net savings automatically put into. There are several columns in between these two.

    My second workbook has the claim numbers that I need to search and find on the first workbook and then return then return the correct net savings that is in a different column on this second workbook. I only can search 9 out of the 11 digits that are in the claim number due to the last two digits are different in each workbook.

    Thank you so much in advance for your help!
    Nadine


    =INDEX('[CORVEL_CLEAN_BILLING 051818 after Clearing.xlsm]CORVEL_CLEAN_BILLING CA 051818'!$G$2:$G$9, MATCH(LEFT('[CORVEL_CLEAN_BILLING 051818 after Clearing.xlsm]CORVEL_CLEAN_BILLING CA 051818'!$H$2,LEN('[CORVEL_CLEAN_BILLING 051818 after Clearing.xlsm]CORVEL_CLEAN_BILLING CA 051818'!$H$2)-2),LEFT(E96:E283,LEN(Claim_number_column: E96:E283)-2),0))
    Last edited by kocumna; 05-25-2018 at 01:39 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Index, match, len

    Welcome to the board. You'll find that you'll generally get responses faster if you include a sample workbook. It helps people see the issue better and it gives them something to work with. There is another advantage: you'll probably get an answer tailored to your need as opposed to "Here is how that kind of thing is done; adapt it to your circumstances." A solution, using your data, will make more sense to you.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Thank you, but where do I find this:

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    I can't seem to locate it.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Index, match, len

    The Go Advanced button is just to the right of the Post Quick Reply Button.

  5. #5
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    I am trouble to upload a file, but it keeps on failing. Would you happen to know why that is?

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Index, match, len

    A couple of things come to mind. It could be the wrong kind of file, but if it is an Excel spreadsheet of any kind, that should not me an issue. I might also be too big. Generally speaking if it's too big, you're including more data than necessary for a sample. However, if you must, compress the file and attach it as a zip file.

  7. #7
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    I have minimized and uploaded my two workbooks. Please let me know if this is enough data to help me figure this out.

    Thanks again,
    Nadine
    Last edited by kocumna; 05-25-2018 at 05:23 PM.

  8. #8
    Registered User
    Join Date
    05-25-2018
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    14

    Re: Index, match, len

    Hi Nadine,

    Enter the following array formula into C6 cell of in Net saving column using Shift + Ctrl + Shift:

    =IFERROR(INDEX('[Sample CLEAN_BILLING 051818 after Clearing.xlsm]CLEAN_BILLING CA 051818'!$D:$D,MATCH(LEFT(A6,9),LEFT('[Sample CLEAN_BILLING 051818 after Clearing.xlsm]CLEAN_BILLING CA 051818'!$E:$E,9),)),"")

  9. #9
    Registered User
    Join Date
    05-25-2018
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    14

    Re: Index, match, len

    Please find the file.

  10. #10
    Registered User
    Join Date
    05-25-2018
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    14

    Re: Index, match, len

    Please find the file with iferror formula to hide errors.

  11. #11
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Shift + Ctrl + Shift ?? Can I not copy and paste this formula into the spreadsheet?

  12. #12
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Would you please help to understand this formula? It only references the one spreadsheet that I need to pull the net savings from. It doesn't reference the original spreadsheet where the net savings needs to go into.

    TIA
    Nadine

  13. #13
    Registered User
    Join Date
    05-25-2018
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    14

    Re: Index, match, len

    Hi Nadine,

    i am not sure about your level of Excel skill. I am going to explain the basic first.

    Whenever you enter formula into sheet and refers to any cell in the sheet, it shows only cells reference (no worksheet or workbook). For example, if you want to enter a formula =A1 into B1 in the same sheet, it will not refer to sheet name or workbook.

    As the formula is entered into 'Sample May 2018 HMO Traditional thru 051818 after Clearing - CA Only' workbook and in Par CA sheet, any reference to other cells in Par CA sheet is not referring to the workbook sheet. In Match formula, it is just referring to A5, MATCH(LEFT(A5,9) .

    I just want to explain Match formula and why we have to use array formula next.

    The first formula LEFT(A5,9) in Match extracts first nine digits from the lookup value

    The second part LEFT('[Sample CLEAN_BILLING 051818 after Clearing.xlsm]CLEAN_BILLING CA 051818'!$E:$E,9) in Match formula extracts first nine digits of all entries and forms a table in memory

    As the second part has to go through every entry in E:E column and extract 9 digits, we have to use array formula.


    if you have further questions, please let me know your level of Excel skill and if you know how to use simple Index and with Match .

    Kind regards

    Saba

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index, match, len

    Quote Originally Posted by kocumna View Post
    Shift + Ctrl + Shift ?? Can I not copy and paste this formula into the spreadsheet?
    Nadine welcome to the forum.

    That was a typo-graphical error. It should be Ctrl + Shift + Enter.
    When properly committed the formula in the formula bar will have "curly braces" {} wrapped around it. You don't type those in. Excel does it for you. If you do type them in you will get an error.

    I hope that helps.
    Dave

  15. #15
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Thank you so much for your help. It worked! I am not a beginner, but I have used, I would say, intermediate formulas for some time now and am anxious to learn more.


    I have a formula set up in my spreadsheet that subtracts this cell from the cell without an amount filled in before it with a dollar amount in it. Would you please help me solve this issue?

    Col. K - Col. L =K1525-L1525
    $1,908.95- =#VALUE!
    Last edited by kocumna; 05-29-2018 at 01:09 PM.

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index, match, len

    I am having difficulty visualizing from the description. Which workbook does this reference?

  17. #17
    Registered User
    Join Date
    05-25-2018
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    14

    Re: Index, match, len

    Thank you Dave for giving correct key combination for entering array formulas.

    I overlooked it

    Kind regards

    Saba

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index, match, len

    You're welcome. You'd do the same I'm sure.

  19. #19
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Good afternoon,

    I have combined the above tables into one worksheet to see if a formula can be set up similar to the above without using the workbook and worksheet name and also using conditional formatting, possibly, to turn the claim numbers and net savings amounts red when they match.

    Please let me know if you can help me with this?

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index, match, len

    Try wrapping the formula in IFERROR...
    =IFERROR(your-formula,"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  21. #21
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    It does have IFERROR in the formula. Also, I am looking to turn the net savings and claim number red once it finds its match.

    Can you please help with that?


    =IFERROR(INDEX('H:\MACROS\[Sample June 2018 HMO - after Clearing – CA only.xlsm]CLEAN_BILLING CA 060818'!$C:$C,MATCH(LEFT(B6,9),LEFT('H:\MACROS\[Sample June 2018 HMO - after Clearing – CA only.xlsm]CLEAN_BILLING CA 060818'!$D:$D,9),)),"")

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index, match, len

    Use this in E...
    =IF(D6="","",C6-D6)

  23. #23
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Good morning!

    I am having trouble getting my formula to work this morning. Would you please help? I am also using the Ctrl Shift Enter and it's giving me this error:
    "Excel found a problem with one or more formula references in this worksheet."

    Below is the formula that I am using. I have attached the spreadsheet with the one line I know should work. TIA

    =IFERROR(INDEX('[Weekly HMO 090718 after Clearing.xlsm]Week Ending 090718 '!$O:$O,MATCH($W5,'[Weekly HMO 090718 after Clearing.xlsm]Week Ending 090718 '!$R:$R,0)),"")
    Last edited by kocumna; 09-11-2018 at 11:32 AM.

  24. #24
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Good morning!

    Would someone please be able to help me with my question above? I never received a response yesterday. TIA

  25. #25
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index, match, len

    Nadine,

    Are you sure you've uploaded the correct files?

    I've looked at both those files. They are short on information. For example I find no errors and no formulas with the other workbook referenced.

  26. #26
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Thank you for responding. When I ran the formula on previous Excel spreadsheets that I have used, it worked fine, but for this spreadsheet, it doesn't want to work. The spreadsheet is put together from a different spreadsheet. Is there a easy way to find out what could be causing this error? I'm assuming something is calculating incorrectly on this spreadsheet in order for the formula to show an error? When I copy a line out of this spreadsheet a put it into a different workbook and then run the formula, it works. It just won't in this workbook. Thanks so much for your help!
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Good morning. I never received a response from anyone on this thread. Can you please help me with this? Thanks so much!

  28. #28
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Good morning! Would you please advise if you can help me in any way? I haven't heard either way. Thank you.

  29. #29
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index, match, len

    Nadine.

    Thank you for your patience.

    I have had numerous difficulties downloading those files. I finally succeeded. The formula in L4 of TEST.xlsx has faulty references to the other workbook. Perhaps that is the problem at your end.

    When I was finally able to resolve those the formula works fine. Whether it is returning correct results I can not say.

    Do this with the files you have uploaded in post #26. Try array entering
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I did not compare this to the formula in your upload TEXT.xlsx. All I know is that when I edited that formula and selected the ranges from the xlsm file I downloaded it worked. I array entered of course.

  30. #30
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Index, match, len

    Good morning,,

    You mentioned that the formula in L4 of TEST.xlsx has faulty references to the other workbook. Would you please tell me how you fixed that? You formula looks exactly the same as mine.

    TIA,
    Nadine

  31. #31
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index, match, len

    I never found what the underlying problem was.

    What I did was:
    1.) Select each of the below references to the *.xlsm file.

    =IFERROR(INDEX('[TEST Weekly HMO 090718 after Clearing.xlsm]Week Ending 090718 '!$O:$O,MATCH($W4,'[TEST Weekly HMO 090718 after Clearing.xlsm]Week Ending 090718 '!$R:$R,0)),"")

    2.) Re select those ranges (point and click). Hit Enter. It worked again just now.
    Last edited by FlameRetired; 10-23-2018 at 02:15 PM.

  32. #32
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index, match, len

    I just found one problem. The wb range references in the upload have an extra space.

    They were 090718 '
    Should be 090718'

    When deleted the formula works at my end.
    Last edited by FlameRetired; 10-23-2018 at 02:14 PM.

+ 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. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  2. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  7. Replies: 3
    Last Post: 05-02-2013, 01:31 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