+ Reply to Thread
Results 1 to 13 of 13

INDEX/MATCH with special characters skipping rows or showing error even though vale exists

  1. 12-16-2023, 08:30 AM #1
    SparkyTheElectrician
    SparkyTheElectrician is offline
    Registered User
    Join Date
    12-16-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    27

    INDEX/MATCH with special characters skipping rows or showing error even though vale exists

    Hi all,

    I am putting together a tool to calculate conductor sizes based on the National Electric Code. My problem is that some wire gauges are called One-Odd which is written 1/0 AWG.

    To calculate the resistance of a conductor, I have to check the gauge I calculated against the table which carries the resistance. Even though 1/0 AWG is in the table I am using Index/Match in, it shows #n/a.
    Other wire gauges are not shown in the table. This is why I want to use an Index/Match. This would allow me to grab the next lower value.

    This is my first post, if you all are missing information, please let me know.

    Thank you!!!

    Please Login or Register  to view this content.
    AC21 is a VLOOKUP itself

    Please Login or Register  to view this content.
    The wire gauges like 1/0 AWG lead with an " ' "

    Excel AC 21.JPG
    Lookup table.JPG
    Reply With QuoteRegister To Reply
  2. 12-16-2023, 08:33 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,301

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 12-16-2023, 11:12 AM #3
    SparkyTheElectrician
    SparkyTheElectrician is offline
    Registered User
    Join Date
    12-16-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    27

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    I attached a sample workbook. I hope this meets the forum's rules.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 12-16-2023, 11:16 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,301

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    I think you need this:

    =INDEX(L4:L22,MATCH(D6,L4:L22,0)-1)
    Reply With QuoteRegister To Reply
  5. 12-16-2023, 01:11 PM #5
    SparkyTheElectrician
    SparkyTheElectrician is offline
    Registered User
    Join Date
    12-16-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    27

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    Thank you. Now I feel like a fool that I forgot the match type. Anyway, learning from my mistakes.

    Thanks a lot and Merry Christmas.
    Reply With QuoteRegister To Reply
  6. 12-16-2023, 01:15 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,301

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    We've all been there and got the T-shirt!

    Merry Christmas to you, too.

    Glad to have helped.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    Reply With QuoteRegister To Reply
  7. 12-16-2023, 01:21 PM #7
    SparkyTheElectrician
    SparkyTheElectrician is offline
    Registered User
    Join Date
    12-16-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    27

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    It's me again. There is one issue that is still persisting. Say the current is 500A, then the AWG jumps to 900kcmil. 900kcmil is not in the Index/Match array. How do I deal with this case?
    Reply With QuoteRegister To Reply
  8. 12-16-2023, 01:23 PM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,301

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    How do you want to deal with it if it's not there?

    If I am understanding correctly, maybe:

    =IFNA(INDEX(L4:L22,MATCH(D6,L4:L22,0)-1),"Not There")
    Reply With QuoteRegister To Reply
  9. 12-16-2023, 08:28 PM #9
    SparkyTheElectrician
    SparkyTheElectrician is offline
    Registered User
    Join Date
    12-16-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    27

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    Sorry for the delay. If the value is not found in the array, I would like to return the next lower one. Say a 900kcmil is not available, instead of "Not There", I'd like to function to look up the next smaller wire size. In this case 750 kcmil.
    Reply With QuoteRegister To Reply
  10. 12-17-2023, 02:46 AM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,301

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    Do you mean this?

    =IFNA(INDEX(L4:L22,MATCH(D14,L4:L22,0)-1),INDEX(L4:L22,MATCH(D14,L4:L22,1)-1))
    Reply With QuoteRegister To Reply
  11. 12-17-2023, 06:26 AM #11
    SparkyTheElectrician
    SparkyTheElectrician is offline
    Registered User
    Join Date
    12-16-2023
    Location
    USA
    MS-Off Ver
    365
    Posts
    27

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    Thanks. This did the trick. Would you mind explaining the code you used?

    I also added to your reputation!!!!! Thanks again!!
    Reply With QuoteRegister To Reply
  12. 12-17-2023, 07:04 AM #12
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,301

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    It's not code - it's a formula.

    =IFNA(INDEX(L4:L22,MATCH(D14,L4:L22,0)-1),INDEX(L4:L22,MATCH(D14,L4:L22,1)-1))

    INDEX(L4:L22,MATCH(D14,L4:L22,0)-1) - return the row before the one that exactly matches with D14 from the range L4:L22

    IFNA(... - but if that produces no match ...

    INDEX(L4:L22,MATCH(D14,L4:L22,1)-1) - return the row before the one that approximately matches with D14 (is the next lowest) from the range L4:L22

    Please mark the thread as SOLVED.
    Last edited by AliGW; 12-17-2023 at 07:15 AM. Reason: Typo fixed.
    Reply With QuoteRegister To Reply
  13. 01-15-2024, 08:43 AM #13
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,301

    Re: INDEX/MATCH with special characters skipping rows or showing error even though vale ex

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    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. [SOLVED] INDEX MATCH RETURN DIFFERENT VALUE IF value exists
    By rayted in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-21-2023, 06:45 AM
  2. [SOLVED] Index, Match and Sumif Functions showing #Value Error
    By Jia26 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-22-2021, 02:11 PM
  3. Need Help with INDEX MATCH skipping rows during drag down in filtered column
    By luduca444 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2021, 04:12 PM
  4. [SOLVED] Index and Match #NA error when value exists
    By jackson_hollon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-13-2016, 01:05 AM
  5. [SOLVED] Index/Match with Multiple rows/columns #NA error
    By L4wd0g in forum Excel General
    Replies: 3
    Last Post: 12-24-2014, 02:34 AM
  6. Index/ Match - error on letter characters
    By mikera in forum Excel General
    Replies: 2
    Last Post: 09-15-2011, 09:04 AM
  7. String Match & Special Characters
    By camikins in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-13-2010, 01:33 PM

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