# IF Statement Using Cell Ranges & References

1. ## 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. Try

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

3. ## 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. 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. ## 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.

6. ## 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

7. ## Thanks!

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

8. ## 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. ## 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. ## 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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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