Need help with IF/Then Range Function

[wrigh1st]

Hello All,


I am trying to do an If/Then with a range and can't quite seem to figure it out.


Example:


If A2 = 1 through 6, value of cell A3 should be 7, if not value would be 0. I am using Excel 2010 and for some reason I was thinking I was able to do this in Excel 2007. Any assistance would be greatly appreciated.

 

[Faseeh]

Hi wrigh1st,


Firstly welcome to the forums! Secondly you could place in Cell A3 like below:


=IF(AND(A2>=1,A2<=6),7,0)


Regards,

 

[wrigh1st]

@Faseeh, thank you for the welcome and thank you for your help that worked perfectly. Would it be possible to add more to the formula if I had other ranges with corresponding values? For example 1-6 = 7, 7-10 = 8, 11-14 = 9, etc?

 

[shrivallabha]

You can try something like this.

Set up a lookup table in C1:D3

[pre]

1	7
7	8
11	9

[/pre]
and then A3 put following formula.

=VLOOKUP(A2,C1:D3,2,1)

 

[wrigh1st]

@shrivallabha I'm not sure that would give me my desired outcome as I will be putting numbers within my range into A2 and would want to get the expected value in A3. So I might drop 6 into A2 which should bring back 7, or drop 10 into A2 which should bring back 8. I don't know how that lookup would account for the ranges.

 

[shrivallabha]

This is called range_lookup as last argument of VLOOKUP is set to 1 and not to 0. Try it.


Here's a link that explains idea in detail:

http://colinlegg.wordpress.com/2012/03/25/binary-searches-with-vlookup/

 

[wrigh1st]

@shrivallabha That was perfect. Thank you!