+ Reply to Thread
Results 1 to 10 of 10

IF Statement Using Cell Ranges & References

  1. #1
    Registered User
    Join Date
    09-13-2007
    Posts
    9

    IF Statement Using Cell Ranges & References

    I'm trying to use an IF statement for referencing a cell's text value within a data range and if there is a match, then go to another cell and display its value, if not then "No Match"

    As an example: Cell R6 contains the text "AA06", the range of data I want to see if "AA06" is present is from E13:M24, if it is present, I want to display a specific cell as the true value and if not "No Match"

    Here is the IF statement I can get to work on a single cell reference:
    =IF(R6=D13,D14,"No Match")

    Here is one that give me a #value! error
    =IF(R7=E13:M24,E17,"NO TX")

    Any help is very appreciated.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try

    =IF(COUNTIF(E13:M24,R7),E17,"NO TX")

  3. #3
    Registered User
    Join Date
    09-13-2007
    Posts
    9

    thank you- it worked- now next challenge

    Thank you very much for your help, I tried your statement and it works. Now comes the second part of the referencing that I forgot to post (Sorry )

    The cell that I found that matches my criteria could be anywhere within the data range that I previuosly stated, I want to take the reference cell and go down 2 cells and display the text or answer in that cell.

    As you can see with the sucessfull formula below, I got a good result based upon what I had stated prior;

    =IF(COUNTIF(D11:M90,R6),D17,"NO TX")

    For better understanding:
    R6 contains the data I'm trying to find with is "AA01"
    D11:m90 is the data range which contains "AA01" and many more data sets as welll
    D17 (MY TARGET DATA) is 2 cells below the match of D15(which is "AA01" Which also is = TO R6

    Thanks again for your assistance, I was trying to accomplish this task longer that I wish to admit, so your help is greatly appreciated.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    That's a slightly trickier proposition, what if R6 occurs more than once in the range?

    Assuming that, if R6 is repeated in D11:M90 that you match with the one in lowest row fthen this approach should do what you want.

    You can use 1 formula but for simplicity here's a method using 2 formulas.

    Assume 1st formula is in A1

    =MIN(IF(D11:M90="R6,ROW(D11:M90)-ROW(D11)+1))

    needs to be confirmed with CTRL+SHIFT+ENTER.

    This gives the row number of the first value of R6 within the range, e.g. if F20 matches R6 this returns 10, because F20 is in the 10th row of D11:M90

    Now in another cell this formula should give the result you want

    =INDEX(D11:M90,A1+2,MATCH(R6,INDEX(D11:M90,A1,0),0))

  5. #5
    Registered User
    Join Date
    09-13-2007
    Posts
    9

    IF STATEMENTS USING CELL rANGES & REFERENCES

    Thanks Again For Your Assistance, I Tried The Last Set Of Formulas And They Did Not Work. (problem Is More Me Than The Formula), So I've Attached The Spreadsheet That I'm Working On To Give A Better Feel For What I'm Doing.

    As Always Any Help Or Suggestions Is Greatly Appreciated.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-14-2007
    Posts
    1

    Thumbs up Solution: user-defined function AreaLookup

    Per my note on justanswer.com attached is zip of the Excel spreadsheet of the user-defined function AreaLookup.

    When you first open this Excel should prompt you that there are macros in the spreadsheet and will likely disable AreaLookup until you enable it. This gives you a chance to verify the macro is what it is before enabling it.

    Let me know how it work for you.

    John
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-13-2007
    Posts
    9

    Thanks!

    Thanks it works great, you'll probably be seeing future posts for other challenges! Very good help, solved the problem. Thanks Again

  8. #8
    Registered User
    Join Date
    09-20-2013
    Location
    Aberdeen, SD
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Thanks!

    In the formula you have it grabbing the cell below by using 1. If you wanted to grab the cell to the right instead, how would you do that?

  9. #9
    Registered User
    Join Date
    11-26-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2003, Open office, google docs
    Posts
    1

    Re: IF Statement Using Cell Ranges & References

    Dear Sir,

    Plz help me on the below case.

    Month Date Place No.of visits
    Apr-13 01/04/13 Jammu 3
    Apr-13 10/04/13 Mumbai 2
    Apr-13 15/04/13 Jammu 1
    Apr-13 15/04/13 Kashmir 1
    .
    .
    .
    .
    .
    Like in column A, Month details, in column B, date, column C, Place and column D, no .of visits given. When ever i enter enter, Apr-13 in column A& Jammu in in column D.
    Like in the above case in a range of entries, I need the sum of all visits to a particular place has to be added up automatically, when ever i choose that place in particular cell.

    Plz help, ur favour is appreciable.

  10. #10
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: IF Statement Using Cell Ranges & References

    srisanmourya

    Welcome to the forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ 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