+ Reply to Thread
Results 1 to 11 of 11

How to use Vlookup(TRUE) to extract values?

  1. 02-21-2021, 09:20 PM #1
    q988988
    q988988 is offline
    Registered User
    Join Date
    07-03-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    42

    How to use Vlookup(TRUE) to extract values?

    I was trying to use the vlookup to find the value matching the target ID that is the nearest to my ID as shown in the image, but the tricky part is that the number it should compare is the digit after the 2nd "_". So the formula did not pick up the proper value.

    Example.png

    I have attached the sample file. Any help is appreciated (does not have to be a "vlookup" - anything that works)!
    Attached Files Attached Files
    Last edited by q988988; 02-23-2021 at 07:25 PM.
    Reply With QuoteRegister To Reply
  2. 02-21-2021, 09:45 PM #2
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,481

    Re: How to use Vlookup(TRUE) to extract values?

    What if: 55B_1_2500 ?
    Does it round to 2000 or 3200?
    Below formula for round-up case:
    Assum targetID is sorting per ID:

    =INDEX($H$2:$H$9,MATCH(TRUE,SUBSTITUTE($G$2:$G$9,LEFT(A2,6),"")+0>RIGHT(A2,LEN(A2)-6)+0,0))
    Attached Files Attached Files
    Quang PT
    Reply With QuoteRegister To Reply
  3. 02-21-2021, 11:46 PM #3
    q988988
    q988988 is offline
    Registered User
    Join Date
    07-03-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    42

    Re: How to use Vlookup(TRUE) to extract values?

    Thanks for help! The results are looking promising! Just got 2 more scenarios that did not seem to work...
    Please see attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-22-2021, 12:15 AM #4
    q988988
    q988988 is offline
    Registered User
    Join Date
    07-03-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    42

    Re: How to use Vlookup(TRUE) to extract values?

    Hi, thanks for the help. I accidently replied without quoting your reply. Please see the latest reply. Thanks
    Reply With QuoteRegister To Reply
  5. 02-22-2021, 02:25 AM #5
    AliGW
    AliGW is online now
    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. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,420

    Re: How to use Vlookup(TRUE) to extract values?

    Administrative Note:

    There is no need to quote posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
    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.
    Reply With QuoteRegister To Reply
  6. 02-22-2021, 02:31 AM #6
    AliGW
    AliGW is online now
    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. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,420

    Re: How to use Vlookup(TRUE) to extract values?

    Why should this be J?

    208_1_3500

    Makes no logical sense compared with the others.
    Reply With QuoteRegister To Reply
  7. 02-22-2021, 02:46 AM #7
    q988988
    q988988 is offline
    Registered User
    Join Date
    07-03-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    42

    Re: How to use Vlookup(TRUE) to extract values?

    J is just a wrong exmaple using the existing formula.

    Please see attached the latest version with the correct headings and the expected return values.

    Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 02-22-2021, 02:48 AM #8
    AliGW
    AliGW is online now
    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. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,420

    Re: How to use Vlookup(TRUE) to extract values?

    Sorry, yes, I see - I misread your workbook.
    Reply With QuoteRegister To Reply
  9. 02-22-2021, 07:23 PM #9
    q988988
    q988988 is offline
    Registered User
    Join Date
    07-03-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    42

    Re: How to use Vlookup(TRUE) to extract values?

    It looks like the Index function is working in this case, but the tricky parts are: 1. the order in the target table is random (sorting might be able to solve this). 2. there are sometimes 4 digits after the 2nd "_" in the IDs (not necessarily 3 all the time). Any help, please?
    Reply With QuoteRegister To Reply
  10. 02-23-2021, 02:17 AM #10
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,615

    Re: How to use Vlookup(TRUE) to extract values?

    In C3 then copy down
    Please Login or Register  to view this content.
    In C36 and copied down
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 02-23-2021 at 02:24 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
  11. 02-23-2021, 07:26 PM #11
    q988988
    q988988 is offline
    Registered User
    Join Date
    07-03-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    42

    Re: How to use Vlookup(TRUE) to extract values?

    Thanks for the 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. Using VLOOKUP to compare values in two workbooks and get a TRUE/FALSE in third
    By usurrao in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2019, 12:28 PM
  2. 2-Criteria Vlookup or other formulae to extract cell values
    By Whitenoise1 in forum Excel General
    Replies: 4
    Last Post: 04-22-2019, 11:34 PM
  3. VLookup One Value and extract multiple values
    By vivek.budhram in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-12-2018, 11:56 AM
  4. Lookup value in a list and return True if all values are true, otherwise false
    By Afsheen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-16-2018, 06:07 AM
  5. [SOLVED] i want to extract 2 field values with vlookup
    By farm58 in forum Excel General
    Replies: 4
    Last Post: 02-17-2018, 03:58 AM
  6. Vlookup True not working - need partial string match and returned values
    By leishab in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-20-2013, 04:44 PM
  7. Vlookup only returning values when true ?!?
    By mx5jmcb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-21-2013, 05:42 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