Comparing two columns

[Carlos]

Hi,

I am tryng to compare one number to a range of numbers.
If the number falls between these two number, then I
should get a result. Here is an example
Sheet 1 Sheet 2
Low HI Rating | Number Result
..01 .1 A | .3 C
..1 .2 B | .15 B
..3 .4 C |

If I had a .15 the result should be B. If I have .3, the
result should be C. I tried using an IF statement but I
have too many results. My IF statement would be longer
that 255 characters.

Any help is greatly appreciated.

Thank you,

Carlos

 

[A.W.J. Ales]

Carlos,

Have a look at the VLOOKUP function.
suppose your range on sheet1 is named RatingTable
(thus 3 columns)

Then on sheet 2 use in the column Result (suppose heading in row 1 and
outcomes starting in column 2;
further : .3 is in column A (thus in cell A2) )

In cell B2 then enter the formula :
=VLOOKUP(A2,RatingTable,3,0)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Guest]

What if the number is not on eaither column, like .1586?
I thought vlookup only match exact numbers.

Thanks

 

[A.W.J. Ales]

You are both correct and false.

The formula as I gave it *does* need an exact match. I should have set the
last argument to 1.

The VLOOKUP does (on the basis of the fourth argument) also give a result
(if the lookuparea is sorted) if the match is not exact.
Thus the formula should have been :
=VLOOKUP(A2,RatingTable,3,1)

In your RatingTable there is a little problem as well by the way :
What rating should be given for "scores" between 0.2 and 0.3 (for instance
2.7)
(The formula as it is now it will return B (since 0.2 is the largest value
in the first column which is smaller than 2.7).





--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[A.W.J. Ales]

Oops, wrong again : The last sentence shoud read :

Since 0.1 is the largest value in the first column which is smaller
than 2.7


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[karry]

carlos

try the index and match functions