+ Reply to Thread
Results 1 to 30 of 30

Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Column

  1. #1
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Column

    I have a spreadsheet where I want to check several things

    I'll try and keep it simple:

    To start, I want to go the function to compare the value in cell A1 with the all the values in Column B.
    If, for example, A1<any cell in Column B, let's say, in the example below, B3 and B4, then I want it look at the correspondent value in Column C, C3 and C4. Then I would want it to return the row in which it found the highest value in column C which, as stated above, has a corresponding value in B that was less than the value in A.


    I'd like to put that formula beside cell A1, so that I can tell which row is, if you will, below budget but has the highest value.


    Something like this:

    A1 = 45



    COL COL COL
    ROW A B C
    1 45 32.3 101
    2 42.6 115
    3 54.3 156
    4 64.1 124
    5 22.3 111

    So, in this example, the result would be 3, since both B3 and B4 are greater than A1, but C3 is higher than C4.

    Of course, columns B and C are pretty long in my real spreadsheet.

    Thanks for any help!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    HI Olives,

    I see only one value in column C, see attached:-


    for olives.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    [QUOTE=dilipandey;3120574]I see only one value in column C...

    Hi didli! Here's the example spreadsheet. There are values in Column C now. I hadn't formatted it right on the forum. Looking forward to hearing from you or anyone with an idea! Thanks!

    for olives.xlsx
    Last edited by olives; 02-11-2013 at 06:19 PM.

  4. #4
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Ok, let me ask by steps.

    How can I get the program, Excel, to look at Cell A1, compare A1 to all the cells in Column B and let me know which cells in Column B are greater than A1?

    Next: Say there are 2 cells in Column B, for example, say B3 and B4, that are greater than A1. How can I then have Excel look at the adjacent column, Column C, for each of these cells, C3 and C4, compare their values and tell me C3 is higher than any other match?...

    Now, if I put numbers from 1, 2, 3 etc... in Column A, Excel could return the value 3, telling me C3, the highest of the values whose adjacent cell in Column B is greater than A1, is on row 3... Can anyone help? A very simple spreadsheet is attached. Thanks!

    for olives.xlsx

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Hi Olives,

    See the attached files with my suggestions / remarks ..

    for olives.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>

  6. #6
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Quote Originally Posted by dilipandey View Post
    See the attached file...
    Wow! So far so good! Pretty nice!... I've made comments and clarified in the spreadsheet and attached it below... I'm impressed, dili...Thanks!

    for olives.xlsx

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    C3 is 156 which is higher among C3 and C4
    but you need 4 which is appearing against C4 .. why ?


    Regards,
    DILIPandey
    <click on below * if this helps>

  8. #8
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Dili, I rushed out for a meeting yesterday and made a mistake I apologize, you're right, I do need 3, which appears against C3... I also apologize because I thought I had replied yesterday, but now I don't see my comment... Thanks!

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    HI Olives,

    Try using below formula:-
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    enter this using ctrl shift enter key combination.

    See attached:- for olives(1).xlsx



    Regards,
    DILIPandey
    <click on below * if this helps>

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Actually I have given 2 formula options in above attachment, choose whatever works for you


    Regards,
    DILIPandey
    <click on below * if this helps>

  11. #11
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Wow, pretty cool... I'm in awe - ! - Thanks! I just looked at the spreadsheet and saw how easy you made it! I'll be setting up the whole file in the next few days... Can't wait to use your formulas! Nice!

  12. #12
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    you are welcome olives

    Please mark this thread as [SOLVED]... Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>

  13. #13
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Dili - Im trying to setup my whole spreadsheet using Option 1 in the spreadsheet you last sent me. I think the formula makes refernce to cells F3 and F4. I guess that means I have to always "create" cells F3 and F4 for Option 1 to work?

    Here's the formula in Option 1:

    =VLOOKUP(MAX(IF(($B$1:$B$5=F3)+($B$1:$B$5=F4),$C$1:$D$5,"")),IF(($B$1:$B$5=F3)+($B$1:$B$5=F4),$C$1:$D$5,""),2,0)

  14. #14
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    I know and thats why I gave option 2 as well


    Regards,
    DILIPandey
    <click on below * if this helps>

  15. #15
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Quote Originally Posted by dilipandey View Post
    ... thats why I gave option 2 as well...
    Ok, let me take this by steps then. Here's the formula in option 2:


    =VLOOKUP(MAX(IF(($B$1:$B$5=IFERROR(INDEX($B$1:$B$5,SMALL(IF($B$1:$B$5>$A$1,ROW($B$1:$B$5),""),ROW($A1))),""))+($B$1:$B$5=IFERROR(INDEX($B$1:$B$5,SMALL(IF($B$1:$B$5>$A$1,ROW($B$1:$B$5),""),ROW($A2))),"")),$C$1:$D$5,"")),IF(($B$1:$B$5=IFERROR(INDEX($B$1:$B$5,SMALL(IF($B$1:$B$5>$A$1,ROW($B$1:$B$5),""),ROW($A1))),""))+($B$1:$B$5=IFERROR(INDEX($B$1:$B$5,SMALL(IF($B$1:$B$5>$A$1,ROW($B$1:$B$5),""),ROW($A2))),"")),$C$1:$D$5,""),2,0)

    Since I have a whole columns worth of data in Column B, can I, and should I substitute all instances of $B$1:$B$5 above with $B:$B? or should I give it the whole range? If I have a column's worth of data in C, too, what other changes should I make? Whew! Mind boogling! Thanks!
    Last edited by olives; 02-14-2013 at 02:18 PM.

  16. #16
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Hi olives,

    If you give entire B column in reference like B:B, it will slow down the calculation.
    Suggest you to to use fixed range if you have like $B:$B500 else you can use defined name as well here.


    Regards,
    DILIPandey
    <click on below * if this helps>

  17. #17
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Are there any changes I have to make referring to the range in Column C?

  18. #18
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Oops, reposted unwittingly!
    Last edited by olives; 02-14-2013 at 03:31 PM.

  19. #19
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    column C should start with row 1 as I entered in the formula.


    Regards,
    DILIPandey
    <click on below * if this helps>

  20. #20
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    ... thats why I gave option 2 as well...
    Dilip - I'm still playing around with your formulas... Am I right that if I paste the formula in Option 2 that it gives me the 2nd highest value after it does its comparisons? Thanks! Let me know, please, 'cause in my example it worked out that way and I'm wondering if that's the case or just a coincidence! Thanks!

  21. #21
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    ...I'm still playing around with your formulas...
    Another question, what if I wanted the first check, in Column B, to be whether the data in each cell in Column is LESSER THAN, rather than greater than, the value in cell A1, would it be easy to make changes to the formula in Option 2 to do this? It would still seek the highest corresponding values in Column C... thanks!

    O.

  22. #22
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    share the Excel workbook with your expected output as per your above posts.. thx


    Regards,
    DILIPandey
    <click on below * if this helps>

  23. #23
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Here we go! Thanks!

    for olives 2.xlsx

  24. #24
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    I used the formula in G3 to find the highest value in Column C from those whose corresponding value in Column B
    G3 is blank and no values in column B


    Regards,
    DILIPandey
    <click on below * if this helps>

  25. #25
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Quote Originally Posted by dilipandey View Post
    G3 is blank and no values in column B...
    I fixed it on the attached spreadsheet..... I moved so much stufff around I forgot to check the references to the appropriate cells... Here we go!

    for olives 2.xlsx

  26. #26
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Still G3 is blank and no values in column B ... I can assume things here but I don't like ***-U-ME


    Regards,
    DILIPandey
    <click on below * if this helps>

  27. #27
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Quote Originally Posted by dilipandey View Post
    Still G3 is blank and no values in column B ...
    Dilip I uploaded the wrong file. I had named my last spreadsheet 'forolives 2' but 2 files appeared in the download manager application and I chose the wrong one. I'm at work right now and don't know how to download the files to check which one is which, so, I uploaded both. I'm sorry. Would you look at both, please? I don't have the original files at work and don't know how to download the files to this computer from the attachment application. I hope you can look at them and check to see which one is the right one. Otherwise I can check when I get home and upload the right one. Thanks! So much!

    [ATTACH=CONFIG]indexOf[/ATTACH]for olives 2.xlsxfor olives 2.xlsx

  28. #28
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Quote Originally Posted by olives View Post
    ... I can check when I get home and upload the right one. Thanks! So much!
    Ok, this the right one. You won't find any values in Column B. It's blank until the desired outcomes section at the bottom of the spreadsheet. But I think all the references are right in my comments. Thanks!


    for olives 2.xlsx
    Last edited by olives; 02-23-2013 at 01:52 PM.

  29. #29
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Hi Olives,

    see the attached file where all your desired outputs has been obtained using formulas:-
    for olives 2.xlsx


    Regards,
    DILIPandey
    <click on below * if this helps>

  30. #30
    Registered User
    Join Date
    02-10-2013
    Location
    PR
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

    Quote Originally Posted by dilipandey View Post
    ... see the attached file...
    I just downloaded it and will check it out tonight! Thanks, Dilip! I'm in heaven!

+ 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