Trouble with Formula

[LzzygrlCharmed]

Below is a formula I am trying to use in cell E206. I am trying to have the formula fill in E206 with either Common, Plentiful, Limited, Rare, Scarce based on the Price in Column F. I am trying to have the formula reference the cells that say 0-10, 10-19.99, 20-29.99, 30-39.99, 40-1,000 as the rule. Example: if the price falls in between 0-10 it will show the word Common in cell E206.

Forumula: =IF(F206=$J$212,"Common",IF(F206=$J$213,"Plentiful",IF(F206=$J$214,"Limited",IF(F206=$J$215,"Rare",(IF(F206=$J$216,"Scarce","ERROR"))))))

Example of Spreadsheet:
	$ -	100	$ -	$-	0-10
Common	$ 9.25	100	$ 0.0000013504	$ 1.99	10-19.99
	$ -	100	$ -	$-	20-29.99
Common	$ 3.06	100	$ 0.0000318750	$ 1.99	30-39.99
Plentiful	$ 12.14	100	$ 0.0000119020	$ 2.99	40-1,000

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[mrmmickle1]

You can complete this task using a regular Vlookup. You just need to set up the reference table correctly:

Here is a simple example:
http://blog.contextures.com/archives/2009/06/01/convert-percentages-to-letter-grades-with-vlookup/

 

[Eric W]

I'd probably rearrange things a little. Somewhere on this sheet put these 2 columns:

0	Common
10	Plentiful
20	Limited
30	Rare
40	Scarce

<tbody>
</tbody>

You could put this on another sheet, but let's call them Y1:Z5. The left column is the lower bound for the range, and the right is the description. Given that, you could use this function based on the price:

=LOOKUP(C5,$Y$1:$Z$5)

where C5 is the price. And that's it.