# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  IF Statement Using Cell Ranges & References

## RMH

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.

----------


## daddylonglegs

Try

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

----------


## RMH

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.

----------


## daddylonglegs

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

----------


## RMH

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.

----------


## jgrabsky

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

----------


## RMH

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

----------


## jwambach

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?

----------


## srisanmourya

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.

----------


## Fotis1991

* 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.

----------

