IF and/OR Help Please

[Jezza25]

Hi I am trying to combine the following 2 formulas, in Cell U6 is number 1 or 2, in cell H6 is a range of formula, as you can see by the ranges below I want it to show a different result for 1 and a certain quantity and 2 and a certain quantity. If you can assist that would be great


Formula 1: =IF(U6=1,IF(H6<5001,"upto 5,000",IF(H6<10001,"5,001 to 10,000",IF(H6<20001,"10,001 to 20,000",IF(H6<50001,"20,001 to 50,000",IF(H6<100001,"50,001 to 100,000",IF(H6<150001,"100,001 to 150,000","No Result")))))))

Formula 2
=IF(U6=2,IF(H6<2001,"upto 2,000",IF(H6<7501,"2,001 to 7,500",IF(H6<15001,"7,501 to 15,000",IF(H6<30001,"15,001 to 30,000",IF(H6<100001,"30,001 to 100,000",IF(H6<150001,"100,001 to 150,000","No")))))))

thanks

Jezza

 

[BiocideJ]

Perhaps this?

=IF(U6=1,LOOKUP(H6,{-1E+99,5001,10001,20001,50001,100001,150001},{"up to 5,000","5,001 to 10,000","10,001 to 20,000","20,001 to 50,000","50,001 to 100,000","100,001 to 150,000","No Result"}),IF(U6=2,LOOKUP(H6,{-1E+99,2001,7501,15001,30001,100001,150001},{"up to 2,000","2,001 to 7,500","7,501 to 15,000","15,001 to 30,000","30,001 to 100,000","100,001 to 150,000","No"}),"Unknown U6 Value"))

 

[xenou]

Hi,

In addition to the in cell formula, you can create lookup tables.

Example:
<img src="http://northernocean.net/etc/mrexcel/20141016_screenshot.png" />

Formula in Cell B6 is:
=IF($B$2=1,INDEX($C$9:$C$16,MATCH(B4,$D$9:$D$16,1)),IF($B$2=2,INDEX($F$9:$F$16,MATCH(B4,$G$9:$G$16,1)),NA()))

Sample Workbook (with test grid):
<a href="http://northernocean.net/etc/mrexcel/20141016_Book1.zip">SAMPLE WORKBOOK</a>
sha256 checksum (zip file): 712e3bdb22501b0365d5678ad8dda6c5bb039ca1df797d7205fa92ae589b743d