+ Reply to Thread
Results 1 to 12 of 12

Vlookup with tolerance

  1. #1
    Registered User
    Join Date
    05-07-2009
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Vlookup with tolerance

    See attached sheet for info. (excel 2007) sheet 611 for reference.

    I am working with gas chromatography and the data I get is in the form of
    Retention Time (RT) and Area
    RT Area
    G12 H12

    I need to compare the retention time from the sample to the calibration (A8,B8)

    There is a slight variation in the retention time from the sample to the calibration. So, there needs to be some sort of tolerance. (this is why vlookup doesnt work)

    How would I go about having a function that essentially searches the the calibration retention time for a retention with in the tolerance of the sample value. Then place both the retention time and the area in columns C and D

    for example
    G16 = 25.529 and matches with B21 (25.535)
    I would then want the value 25.529 and 2883.279 placed in columns C and D

    Thank you in advance for anything that you can help me with.
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Vlookup with tolerance

    Welcome to the forum.

    1. Is this a one-time thing?

    2. Can the blanks in column B be eliminated?

    3. Can the blanks in columns G and H be eliminated?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-07-2009
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Vlookup with tolerance

    1) No it is not a one time thing, i have hundreds of samples :-(
    2) I think i need to clarify the mono (G) can only look for retention times in the Mono group (A8 to A10) (retention times in B8 to B10)
    Alternatively the Di can only search in the dichloro group.
    For example
    G16 would search for a match in only B12:B19 (each array i can set up its just some samples have 100s of retention times)

    So I guess Im looking for a way to search each group as a single array.

    Thank you for the welcome
    Last edited by Palabalo; 05-07-2009 at 03:21 PM. Reason: extra clarificaiton

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Vlookup with tolerance

    The layout of the data is inconvenient from a Excel perspective.

    Ideally, data would be arrange in the left side and right side as records, with a separate column for each type of data and no blanks. Is that possible, or is the layout sacred?

    It could be done as is, but messily, and perhaps difficult for you to maintain when whatever changes changes.

  5. #5
    Registered User
    Join Date
    05-07-2009
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Vlookup with tolerance

    The A and B are pretty sacred (lab group standard)

    The sample data is very flexible (imported by group)

    I uploaded another sheet for further clarification.
    Attached Files Attached Files

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Vlookup with tolerance

    Are the names in col A (Mono, Dichloro, ...) fixed, or subject to change?

    Will the number of rows in each remain constant?

  7. #7
    Registered User
    Join Date
    05-07-2009
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Vlookup with tolerance

    The names are flexible (i could make them match the column headings for the sample)
    the rows are highly variable for the samples but the A B C table is constant

  8. #8
    Registered User
    Join Date
    05-07-2009
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Vlookup with tolerance

    Is this possible? If it is possible, i would save 100s of hours manually collecting data.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Vlookup with tolerance

    Still pondering, mon frere.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Vlookup with tolerance

    In your example, only one of the three lines in K/L is mapped. What about the other two?

  11. #11
    Registered User
    Join Date
    05-07-2009
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Vlookup with tolerance

    The hope would be that I could place the function in col C and it would pull all the data from the K/L column

    some thing to the effect of C24 =vlookup(B24,$K$7:$L$9,1,false) Then copied cell from C24 to C43
    But the issue is the values in the K column will almost never exactly equal the B column and any rounding will give me issues.

    to the effect of

    adding a tolerance on the search of vlookup where it would return the "1" if the value in the table was found in the range "B24+tolerance, B24-tolerance".

    To answer your question, the goal is to map all k/l that are only a tolerance away from a B value

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Vlookup with tolerance

    The reason I ask is that in your last example, the closest match for K8 and K9 is B41 ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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