+ Reply to Thread
Results 1 to 16 of 16

Searching for an exact numeric match with a formula

  1. #1
    Registered User
    Join Date
    11-23-2022
    Location
    Sao Paulo
    MS-Off Ver
    Excel for Mac
    Posts
    15

    Question Searching for an exact numeric match with a formula

    Hi folks, thanks for having me here, my first post after crawling around the internet and looking for answers. You seem the right people to save me!

    So here's my situation: I'm running a fairly complex formula to search for AN ID (ID in COLUMN B) in an array (COLUMN G), and return the value of a different column (VALUE in COLUMN H) when found.
    However, my formula is giving me a match for partial matches, not exact matches, as such:

    If I search for 5.1, it's also returning results for 15.1, or 5.11.

    Furthermore, I'd like the results to have a carriage return between them, but it doesn't seem to be working.

    I thank you all in advance, and I'm keen to share the knowledge I have y'all.
    Attached Files Attached Files

  2. #2
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,396

    Re: Searching for an exact numeric match with a formula

    Please try in C4 and copy down (and click 1 or 2 times wrap text):

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,396

    Re: Searching for an exact numeric match with a formula

    Slightly shorter formula and a bit clearer written down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Before you paste the formula, put the cursor in the formula bar.
    Attached Files Attached Files

  4. #4
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,396

    Re: Searching for an exact numeric match with a formula

    Thanks for the rep and feedback. .

    Finding a formula was a fun puzzle, which I also learned from.

  5. #5
    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
    80,351

    Re: Searching for an exact numeric match with a formula

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
    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.

  6. #6
    Registered User
    Join Date
    11-23-2022
    Location
    Sao Paulo
    MS-Off Ver
    Excel for Mac
    Posts
    15

    Re: Searching for an exact numeric match with a formula

    and I though my formula was complex! :D
    Thank you so much for your help mate. I'll break down the formula you wrote and try to learn from it.

    Cheers

  7. #7
    Registered User
    Join Date
    11-23-2022
    Location
    Sao Paulo
    MS-Off Ver
    Excel for Mac
    Posts
    15

    Re: Searching for an exact numeric match with a formula

    Hi mate,
    Unfortunately the formula you posted worked fine for the sample, but did not work for my original sheet.

    The main difference betwee the two is that the original sheet doesn't have tables within it. I tried to replace the table references you have in your formula by the respective ranges, but it didn't work. What am I doing wrong?

  8. #8
    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
    80,351

    Re: Searching for an exact numeric match with a formula

    For future reference, make sure that SAMPLE data accurately reflects the REAL data in form and layout.

  9. #9
    Registered User
    Join Date
    11-23-2022
    Location
    Sao Paulo
    MS-Off Ver
    Excel for Mac
    Posts
    15

    Re: Searching for an exact numeric match with a formula

    Thanks for the heads up. Should I reupload the amended sample then?

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,577

    Re: Searching for an exact numeric match with a formula

    In C4 then copy down

    =TEXTJOIN(CHAR(10),TRUE,FILTER($H$4:$H$8,ISNUMBER(SEARCH("-"&B4&"-","-"&SUBSTITUTE($G$4:$G$8,CHAR(10),"-")&"-")),""))

    First Format cells for Wrap text. After that you apply formula. Reverse way it will not work. After Formatting only formula should be executed.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  11. #11
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,396

    Re: Searching for an exact numeric match with a formula

    Should I reupload the amended sample then
    Yes please

  12. #12
    Registered User
    Join Date
    11-23-2022
    Location
    Sao Paulo
    MS-Off Ver
    Excel for Mac
    Posts
    15

    Re: Searching for an exact numeric match with a formula

    I've reuploaded a more accurate example of what I'm trying to achieve. Thank you so much once again.

    PS: the original Source spreadsheet is massive. Would there be any issues with memory handling given the amount of text this formula has to parse?
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,577

    Re: Searching for an exact numeric match with a formula

    Pl see Post #10

  14. #14
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,577

    Re: Searching for an exact numeric match with a formula

    IN C3 then copied to full range.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  15. #15
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,396

    Re: Searching for an exact numeric match with a formula

    I had made my formula a bit too long last time. Based on the ideas of kvsrinivasamurthy try in C3 and copy right and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Be careful when searching for 5.10. To distinguish with 5.1 you have to place a single quote for 5.10. See attached workbook.

  16. #16
    Registered User
    Join Date
    11-23-2022
    Location
    Sao Paulo
    MS-Off Ver
    Excel for Mac
    Posts
    15

    Re: Searching for an exact numeric match with a formula

    Thanks all, both your answers were very useful and helped me to arrive at the right formula.

+ 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] How to fit in Exact Match into an Index and Match formula that contains Isnumber
    By y0urd00md in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-19-2021, 07:27 AM
  2. MATCH formula without an exact numeric match
    By jonnyhtfc90 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-06-2019, 08:35 AM
  3. Replies: 1
    Last Post: 03-25-2019, 12:24 PM
  4. [SOLVED] Amending an IF formula from an exact match to partial match
    By pauldaddyadams in forum Excel General
    Replies: 2
    Last Post: 03-02-2017, 05:11 PM
  5. [SOLVED] vba to replace string from exact match list by searching approximate value
    By johnlara in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-18-2015, 10:12 AM
  6. Searching a list and finding the exact match from a sublist
    By Lattaio23 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-03-2012, 11:19 AM
  7. Searching Range for Exact Match
    By SATEAT in forum Excel General
    Replies: 4
    Last Post: 05-20-2010, 07:14 PM

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