+ Reply to Thread
Results 1 to 12 of 12

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

  1. 03-21-2017, 07:15 AM #1
    Karlss
    Karlss is offline
    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.
    Reply With QuoteRegister To Reply
  2. 03-21-2017, 07:36 AM #2
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    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.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  3. 03-21-2017, 08:08 AM #3
    Karlss
    Karlss is offline
    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.
    Reply With QuoteRegister To Reply
  4. 03-21-2017, 02:58 PM #4
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,604

    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
    Reply With QuoteRegister To Reply
  5. 03-21-2017, 05:33 PM #5
    Karlss
    Karlss is offline
    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!
    Reply With QuoteRegister To Reply
  6. 03-21-2017, 11:37 PM #6
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,604

    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
    Reply With QuoteRegister To Reply
  7. 03-22-2017, 05:08 AM #7
    Karlss
    Karlss is offline
    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.
    Reply With QuoteRegister To Reply
  8. 03-22-2017, 09:59 PM #8
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,604

    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
    Reply With QuoteRegister To Reply
  9. 03-23-2017, 06:19 AM #9
    Karlss
    Karlss is offline
    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
    Reply With QuoteRegister To Reply
  10. 03-23-2017, 06:28 AM #10
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,169

    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.
    Reply With QuoteRegister To Reply
  11. 03-23-2017, 08:02 AM #11
    Karlss
    Karlss is offline
    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!
    Reply With QuoteRegister To Reply
  12. 03-23-2017, 08:22 AM #12
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,169

    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 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. how do i "search" a column and return the value of its adjacent cell
    By tracym239 in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1