Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    I've tried everything I can think of and just can't figure this out. I have a numeric value that I need to compare to the value in 2 different columns. It must be >= the value in the first column and < the value in the second column. Once I've found that, I need to return a value in that same row, but from which column would be contingent on matching yet another value. Can anyone help with this....or does it even make sense? I can supply a snippet of data as needed.

  2. #2
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    An example would definitively be useful. I see something like:
    =IF(AND(first condition >=, second condition <), INDEX(range, HLOOKUP(...), ROW()), False expresssion)
    This eco-post is made of recycled electrons

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's small sample of data. Bottom line is the Value in A3 has to be between the values in columns E and F, >= E and less than F. Once those cells/row is found, the value returned will be based on where a match is found to B2 looking at G2:Q2.

    Since A3 = 247.75, the >= and < figures would be found in E12 and F12, so the row for the return value would be 12. The column the value is returned from will be based on where the value in B2 (in this case 3) is found in G2:Q2. Since 3is found in cell J2, the return value should be 45 from cell J12.

    Hope this helps. I tried to apply the formula you entered and still couldn't make it work.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I now have a better understanding.
    I will put in A3: =MATCH(A2,E2:E12,1)-1
    And in B3: =MATCH(B2,G2:Q2,0)
    And in C3: =INDEX(G3:Q12,A3,B3)

    Please let me know if it answers your question. If you want to test for #N/A, you will have to use an IF(ISNA(MATCH(B2,G2:Q2,0)),"no B match",MATCH(B2,G2:Q2,0))
    This eco-post is made of recycled electrons

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It almost answers the question. I did what you said and it returns 44. From what I can disect in the formulas it looks like you checked if A2 were less than the values in column E, then based on where B2 was found in G2:Q2, you returned 44.

    The thing is the value in A2 (247.75) has to be equal to or greater that a value in column E.....AND it must be less than the corresponding value in column F. In this case that would fall under row 12, so it should return 45. Sorry to be so thick, but for future reference, can you tell me what the -1 means at the end of the formuala =MATCH(A2,E2:E12,1)-1? I'm close to understanding, but not quite there yet. Thanks for the patience.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    =INDEX($G$3:$Q$12,MATCH(A2,$F$2:$F$12),B2+1)

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Works perfectly, thanks! I would like to understand this so I can apply it to other situations. In the Match portion of the formula, if a match type is not specified, is the default the next higher number found? And in the B2+1 portion, why do you need +1?

    Thanks again - I always get great info from loungers.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If no match type is specified it defaults to finding the value less than or equal to the lookup value. Thus in your example the "match row" found is for the value row with 140 in Col B. The formula adjusts for this by starting the index range 1 row down...

    I used B2 +1 since the index starts with 1 and your columns start with a zero so the "index column" needed is 1 more than the value in B2

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Makes perfect sense now. Thanks so much for all the great help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •