IF Formulas

  • Thread starter Thread starter neil_stafford
  • Start date Start date
N

neil_stafford

Hi All

Im having a problem, I have the following formula:

=IF(D5="Standard Soft Start & optimiser","25",IF(D5="mechanica
press","15",IF(D5="injection moulding 1","15",IF(D5="injection mouldin
2","15",IF(D5="conveyor_on","15",IF(D5="conveyor_off","20",IF(D5="scre
compressor","10",IF(D5="reciprocating compressor","5","Error"))))))))

All works fine, BUT it wont let me put any more IF commands in there
I need to do another 6!!!!


Any ideas?

Thanks

Nei
 
You can only nest up to 7 IF()s. Time to migrate ...

Here's one way using OFFSET( .. MATCH(...)..)
[ VLOOKUP can also be used ]

In a new Sheet2 (say), set-up a 2 col table in say, cols A & B
with the part description in col A and part# in col B, row1 down:

Standard Soft Start & optimiser....25
mechanical press............................15
injection moulding 1.......................15
injection moulding 2.......................15
etc

In Sheet1, say, assuming the part description is in col D, D2 down

Put in say, E2:

=IF(ISNA(OFFSET(Sheet2!$A$1,MATCH(TRIM(D2),Sheet2!A:A,0)-1,1)),"Error",OFFSE
T(Sheet2!$A$1,MATCH(TRIM(D2),Sheet2!A:A,0)-1,1))

Copy E2 down col E as many rows as there is data in col D

Col E will return the matching part# for the part description in col D

If no match is found for the part description in col D, "Error" will be
returned

TRIM() is used to increase the robustness of the match, in case there are
inadvertent
leading, trailing or "extra" in-between spaces in the part description
entered in col D
 
Neil

One way, Set up a table with IdNo, Description, Qty? and
enter the details on a blank page. If this table is named
parts then the lookup formula with call the details from
the table based on the Id being entered in the active
sheet.

=vlookup(a2,parts,2,false) returns the desription
=vlookup(a2parts,3,false) returns the qty.

with this you only have to enter the id in the working
sheet.

There is a limit when using Nested Ifs.

Regards
Peter
 
=VLOOKUP(D5,{"Standard Soft Start & optimiser","25";"mechanical
press","15";"injection moulding 1","15";"injection moulding
2","15";"conveyor_on","15";"conveyor_off","20";"screw
compressor","10";"reciprocating compressor","5"},2,FALSE)

Add extra values as required.

Best to put values and such in a table and call like

=VLOOKUP(D5,H1:I20,2,FALSE)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks ever so much guys, you have been a great help. all sorted now!.

WHY AM I WORKING ON A SUNDAY???!!!! lol

Have a good weekend

Nei
 
Neil, you're welcome !
Thanks for the feedback.
[ from us guys who work best? on Sundays <g> ]
 
Back
Top