Returning a text result when cell value falls within a range

[oatsiebfc]

Hi new to the forum, thanks for any help.

I need to create a formula that returns different text values that are determined by the value in another cell falling within a specific range.

For example, if H42's value is anywhere between 0 and 6 I want I42 to display "BL1". If H42's value is anywhere between 7 and 12 I want I42 to display "1C", between 13 and 17 is "1B" and 18+ is "1A". I have tried doing this with the IF function but having no luck at all.

Thanks for any help.

[Olly]

Try:

Please Login or Register  to view this content.

[martindwilson]

Assuming you are only entering integers
=IF(H42="","",LOOKUP(H42,{0,7,13,18},{"BL1","I42","1B","1A"}))

[AliGW]

Copy and paste this into I42: =LOOKUP(I42,{0,7,13,18},{"BL1","1C","1B","1A"})

[bebo021999]

Try:

=IF(H42>=18,"1A",IF(H42>=13,"1B",IF(H42>=7,"1C",IF(H42>=0,"BL1","display what you want"))))

Or:

=LOOKUP(H42,{0,7,13,18},{"BL1","1C","1B","1A"})

[martindwilson]

with lookup you need to test for blank first else lookup assumes the cell is 0 and will always return BL1

[AlKey]

Try this formula

=INDEX({"BL1","1C","1B","1A"},MATCH(H42,{0,7,13,18}))

[oatsiebfc]

Amazing work, thanks to you all. It is working a treat

[martindwilson]

dont want to be picky but
aligw =LOOKUP(I42,{0,7,13,18},{"BL1","1C","1B","1A"}) is the wrong cell reference a typo?
alkey =LOOKUP(H42,{0,7,13,18,100},{"BL1","1C","1B","1A"})
you dont need anything after 18 if 100 or above you'll get #n/a
and as i said before
with lookup you need to test for blank first else lookup assumes the cell is 0 and will always return BL1

[AliGW]

dont want to be picky but
aligw =LOOKUP(I42,{0,7,13,18},{"BL1","1C","1B","1A"}) is the wrong cell reference a typo?

Yes, should be J42, but I think the OP has used a different version, so no harm done.