+ Reply to Thread
Results 1 to 12 of 12

Search for number in column, then search for "number greater than" in adjacent row.

  1. #1
    Registered User
    Join Date
    03-21-2017
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    6

    Search for number in column, then search for "number greater than" in adjacent row.

    Hi!

    I'm trying to find value from a table based on two criteria. Let's say from A1 and A2 on sheet1.
    First I want to search a spesific column in sheet2 (ex. sheet2!D4:D51) for a number "equal too" A1. This is the easy part.
    When I have located the correct number, I want to search the adjacent cells for value greater than A2. Then I want this number returned to sheet1!A3. How is this part done?

    I'm trying to automaticly extract DN and SCH values for steel pipes from a table. A1 and A2 are actual pipe diameter and minimum wall thickness. The A3 Cell will then be the actual wall thickness. If anyone was interested. :)
    Attached Files Attached Files
    Last edited by Karlss; 03-21-2017 at 05:32 PM.

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    If you attach a workbook (not pictures) with some data and the expected output it will be much easier to help you.

  3. #3
    Registered User
    Join Date
    03-21-2017
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    6

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    Removed link.
    Last edited by Karlss; 03-21-2017 at 05:33 PM.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,901

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to Quick Post button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    03-21-2017
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    6

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    Thank you protonLeah!
    Attachment now in original post!

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,901

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.


    Named ranges:
    NPS_OD: =Sheet2!$D$4:$D$51
    od: =Sheet1!$A$1
    WallThicknessChart: =Sheet2!$E$4:$S$51
    MinWallThickness: =Sheet1!$B$2
    DN:=Sheet2!$C$4:$C$51
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-21-2017
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    6

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    Thank you!
    So if I'm using this for multiple values in a table, I would Use none-absolute address for od and MinWallThickness named ranges? (=Sheet1!$A1 and =Sheet1!$B2)?
    Edit:
    Nope, that didn't work as expected.. :P
    Last edited by Karlss; 03-22-2017 at 06:40 AM.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,901

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    If you want a column of OD's in column A on sheet1 then delete the named ranges OD and MinWallThickness. Then modify the formula in column C as:
    Please Login or Register  to view this content.
    and column D as:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-21-2017
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    6

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    I have tried this, and I can't seem to get it working. It works great in your document, but when I try to get it working in another document.
    It just seem to be choosing the lowest value, no matter what I put in the "min" column.
    When I press the cell to edit, there are no {<-- Brackets like this either -->} to indicate array, like there are in your file.

    New attachment. I have colored the actual columns in the first sheet red.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,826

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    Click on formula in M4

    Then hold down Ctrl+Shift keys and hit Enter

    Then copy/drag formula down

    Repeat for M28

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  11. #11
    Registered User
    Join Date
    03-21-2017
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    6

    Re: Search for number in column, then search for "number greater than" in adjacent row.

    Quote Originally Posted by JohnTopley View Post
    Click on formula in M4

    Then hold down Ctrl+Shift keys and hit Enter

    Then copy/drag formula down

    Repeat for M28

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    That fixed it!
    Thanks a lot! To all who helped!

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,826

    Re: Search for number in column, then search for "number greater than" in adjacent row.

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

+ 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: 6
    Last Post: 04-16-2016, 11:04 AM
  2. [SOLVED] Fastest method to search BT column for value starting with "27W" and then adjust adjacent
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-03-2015, 05:50 AM
  3. 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}
    By michaelproctor001 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-10-2013, 02:13 PM
  4. [SOLVED] search for a "part number" within a string and return its cell position or contents of cel
    By benakil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-24-2013, 06:37 AM
  5. [SOLVED] Search for the part number start from "91SE" from every entry
    By Adamlearnexcel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-06-2012, 11:06 PM
  6. search column "A" for every occurrence of the current part number cont.
    By Zombie79 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2007, 09:38 AM
  7. search column "A" for every occurrence of the current part number
    By Zombie79 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-15-2007, 01:44 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