+ Reply to Thread
Results 1 to 6 of 6

Find a value based on 2 search criteria

  1. #1
    Registered User
    Join Date
    07-16-2019
    Location
    Edmonton, Canada
    MS-Off Ver
    Office 365 Business
    Posts
    3

    Cool Find a value based on 2 search criteria

    Hi All,

    This is my first time at this so bare with me as I attempt to explain my problem.

    I am using a Borehole (BH) Logging software where I can export my data into an Excel file from Access. Attached are the two tables that I am working with. The first is called 'Samples' which is a list of samples taken from specific BH at specific depths. The second 'Lithology' which is the depth range of lithologies in each BH. I want the spread sheet to determine what lithology the sample is located in for a specific BH. If the sample was taken from BH 19-B01 at 0.75 m, then on the lithology sheet it should look up BH 19-B01, compare 0.75 m to the depth range of the lithology, and then report the lithology in the sample sheet and do it for the correct borehole every time.

    I have no problem executing this for a single BH using some different functions (index/match, vlookup, lookup), currently I use this in the "I" column of the samples tab =LOOKUP(C2,Lithology!$F$2:$F$5,Lithology!$H$2:$H$5), but I run into an issue once my borehole number changes. I've tried nesting multiple lookup and if functions and am getting lost in the logic. It is becoming monotonous to adjust the above formula, and I am looking for something that I can drag down the data set without adjusting the formula.

    I had a hard time explaining what I trying to do, but if you can take a look at the file it might clear some things up. Please let me know if you have any further questions.

    Thanks!

    Kyle
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Find a value based on 2 search criteria

    are you looking to match two items or are you looking to see if the borehole and depth are "covered" somewhere in the lithology tab?
    for example, (in Samples tab) A2 is in Lithology but C2 (the depth) isn't there for that borehole number. So what should that answer be?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Find a value based on 2 search criteria

    this appears to work (in Samples tab - cell I2), see what you think...
    =LOOKUP(2,1/(Lithology!$D$2:$D$250=A2)/(Lithology!$F$2:$F$250<=C2)*(Lithology!$G$2:$G$250>=C2),Lithology!$H$2:$H$250)
    basing it on assuming you want to find out if 19-B01 and depth of .075 falls between (or matches) what is on the Lithology tab for top depth and bottom depth for that borehole.

  4. #4
    Registered User
    Join Date
    07-16-2019
    Location
    Edmonton, Canada
    MS-Off Ver
    Office 365 Business
    Posts
    3

    Re: Find a value based on 2 search criteria

    ** Columns A and B in lithologies are unrelated**

    I want I2 (samples) to check if C2 (samples) is between F2 and G2 (lithology), if it is - report H2 (lithology), if not check F3 and G3, then report H3 and so on, but you can only compare 19-B01 to another 19-B01 and so on.

    In this case I2 should report 'Silt', because C2 would fall into the range of F3 and G3, and H3 is 'silt'.
    I4 'clay till' C4 F4 and F4, H4 'till'.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Find a value based on 2 search criteria

    I'm assuming your answer in post #4 is to my question in post #2, see how the formula in post #3 I gave you works out.

  6. #6
    Registered User
    Join Date
    07-16-2019
    Location
    Edmonton, Canada
    MS-Off Ver
    Office 365 Business
    Posts
    3

    Re: Find a value based on 2 search criteria

    That works out wonderfully. Thank you.

    Can you explain how the formula works? I may have to slightly manipulate similar data sets in a similar manner.

+ 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. Replies: 2
    Last Post: 10-09-2018, 09:31 AM
  2. [SOLVED] find n. row in ascending order based on search criteria
    By Berna11 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-10-2018, 08:05 AM
  3. Search using .Find with two criteria
    By enhydra in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2018, 01:21 PM
  4. Find and Delete rows based on cell value VBA, using a list as search criteria
    By ang374 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2015, 10:55 AM
  5. Find data based on search criteria
    By melnahum in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-20-2015, 12:01 PM
  6. Replies: 4
    Last Post: 01-08-2013, 12:37 PM
  7. [SOLVED] How to find a value with two search criteria
    By hun1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2013, 04:29 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