+ Reply to Thread
Results 1 to 9 of 9

Lookup in columns and return if the condition is match

  1. #1
    Forum Contributor
    Join Date
    09-10-2010
    Location
    europe
    MS-Off Ver
    Excel 2007
    Posts
    149

    Lookup in columns and return if the condition is match

    Search columns 1,2 and 3 and returns the result to all the data from column A to have a higher or lower number in Column3
    Looking numerical line I need to find results that are within the allowable values

    I want to search a range of data and return the name if the value inside of a specific value.
    The rest of the problems I'm handling the VLOOKUP function
    I need a formula that will return the names that meet the requirement. If necessary we can also add an extra help column.
    If I am in cell G3 or K3 enter another number then the result should be adapted to the number of basic data

    Can someone help me, my example was added to the post,
    I need formula for cells G3:G11 and K3:K11
    Attached Files Attached Files

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Lookup in columns and return if the condition is match

    Could you shed some light let say on this:
    B- 255-10%=229,5 no match 200<22%from255

    So 200 is less than 299(255+22%) or 229,5 is less than 299?
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Contributor
    Join Date
    09-10-2010
    Location
    europe
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: Lookup in columns and return if the condition is match

    Quote Originally Posted by RobertMika View Post
    Could you shed some light let say on this
    Hi RobertMika
    Thank you for answering

    200 is not in the the area of 10% compared to the 255
    I have added a picture for easier understanding
    Attached Images Attached Images

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

    Re: Lookup in columns and return if the condition is match

    How about this then? The workbook you posted is in excel 2003 format but since it says in your profile that you are using excel 2007 I post in that format. I think the IFERROR is the only function that I used that is not compatible with excel 2003,
    Attached Files Attached Files
    <----- 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.

  5. #5
    Forum Contributor
    Join Date
    09-10-2010
    Location
    europe
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: Lookup in columns and return if the condition is match

    Quote Originally Posted by Jacc View Post
    How about this then?
    Hi Jacc,
    Thanks for answer
    Your idea of ​​a solution is great if used files *.xlsx
    But how to solve if using Excel 2003, is there a way?
    Thank you for this solution

    And therefore the problem is not resolved by the end.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup in columns and return if the condition is match

    If you meam the formula on column K, try this ARRAY....
    =IF(ISERROR(INDEX($A$2:$A$10,SMALL(IF($D$2:$D$10<0,IF($D$2:$D$10>=$K$2,ROW($A$2:$A$10)-1)),ROW(F1)))),"-",INDEX($A$2:$A$10,SMALL(IF($D$2:$D$10<0,IF($D$2:$D$10>=$K$2,ROW($A$2:$A$10)-1)),ROW(F1))))
    ...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. Press F2 on that cell and try again.

    Please note, the reason we ask for the excel version you are using, is for this very reason. The member gave a formula that will work with 2007 - as per your profile - but it seems you need it for pre-2007?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Contributor
    Join Date
    09-10-2010
    Location
    europe
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: Lookup in columns and return if the condition is match

    Quote Originally Posted by FDibbins View Post
    Please note, the reason we ask for the excel version you are using, is for this very reason. The member gave a formula that will work with 2007 - as per your profile - but it seems you need it for pre-2007?
    Hi Dibbins
    Thank you for answering
    Formula is OK for Excel 2003


    I want to mention.
    It is true that I use Excel 2007, but I like to have a formula for both the version of Excel 2003 and 2007.
    So I am pleased with the decision and the formula set by the Jacc and with this formula you're set.

    Thank you once again. Problem solved

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

    Re: Lookup in columns and return if the condition is match

    Glad to hear it works. Thanks for converting the formula, FDibbins.

  9. #9
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Lookup in columns and return if the condition is match

    With a small delay but here is my input
    You do not realy need helper column:

    =IFERROR(INDEX($A$2:$A$10,SMALL(IF($D$2:$D$10>=0,IF(($C$2:$C$10)/($B$2:$B$10)-1<=$G$2,ROW($A$2:$A$10)-1)),ROWS($F$1:F1))),"-")
    and is better to use ROWS not ROW to prevent errors in case a new column/row is added.
    For Excel 2003:
    F1:

    =SUMPRODUCT(--($D$2:$D$10>=0),--(($C$2:$C$10)/($B$2:$B$10)-1<=$G$2))
    G2:
    =IF(ROWS($F$1:F1)>$F$1,"-",INDEX($A$2:$A$10,SMALL(IF($D$2:$D$10>=0,IF(($C$2:$C$10)/($B$2:$B$10)-1<=$G$2,ROW($A$2:$A$10)-1)),ROWS($F$1:F1))))
    is much more faster.

+ 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. [SOLVED] Lookup score and match against table with condition
    By PWinkz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-04-2013, 02:09 PM
  2. [SOLVED] Lookup match index multiple values return other columns
    By martypocock in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 11-07-2012, 10:57 PM
  3. lookup or match - Return nearest match
    By devouk in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-05-2012, 06:11 AM
  4. Replies: 3
    Last Post: 12-12-2011, 01:11 PM
  5. Match two condition and return a value
    By magic_ma in forum Excel General
    Replies: 3
    Last Post: 12-09-2011, 09:13 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