IF condition with multiple criteria

[nikhil0311]

Book1
	A	B
1	Revenue	Comment
2	100	$100 mm
3	75	$75mm - $99mm
4	50	$50mm - $74mm
5	45	$15mm - $49mm
6	10	$0mm - $14mm
Nested IF

column A has numbers and column B is the final comment i am looking to capture based on the below criteria.
For example
if the number in column A is (>= 100) greater than and equals to 100 then comment in column B should be "$100 mm"


Criteria
If >= 100 then "$100 mm"
OR
If >= 75 and <=99 then "$75mm - $99mm"
OR
If >= 50 and <=74 then "$50mm - $74mm"
OR
If >= 15 and <=49 then "$15mm - $49mm"
OR
If >0 and <=14 then "$0mm - $14mm"


let me know if you have any questions!

 

[Marcílio_Lobão]

nikhil0311, Good afternoon.

You can use a little table and use a VLOOKUP function with perfects results.

Do you want a solution or you need an IF function solution?

 

[Aladin Akyurek]

Sort the table you have in ascending order on column A (on Revenu).

Now yoy can invoke:

=LOOKUP(X2,Table)

where X2 houses a revenue of interest for which you want to fetch the appropriate comment.

 

[nikhil0311]

it would be great if i get the formula with if conditions

 

[nikhil0311]

i didn't understood x2. can you please elaborate further?

 

[Marcílio_Lobão]

nikhil0311, Good afternoon.

Using the IF function in this case is not the best technical solution.

But if this is what you want, try this formula:

E1 is cell to be searched.

F1 --> formula -->

=IF(E1>=100,"$100 mm",IF(E1>=75,"$75mm - $99MM",IF(E1>=50,"$50mm - $74mm",IF(E1>=45,"$15mm - $49mm",IF(E1>=10,"$0mm - $14mm","----")))))

Is that what you want?

I hope it helps.

 

[Aladin Akyurek]

i didn't understood x2. can you please elaborate further?

If X2 or some other cell houses a look up value (revenue) for which you want to fetch the appropriate comment...

 

[nikhil0311]

Yes. Thank You and have a great day!

 

[nikhil0311]

Thank You Aladin.

 

[Aladin Akyurek]

Yes. Thank You and have a great day!

Thank You Aladin.

Glad to help. Thanks for the update.