Question About IF Function and LOOKUP

  • Thread starter Thread starter Ryan F
  • Start date Start date
R

Ryan F

I am programming a spreadsheet for work which is a defect code list sent to
customers to authorize returns.

The defect codes are 3 digit numbers that correspond to that particular
defect... here is my current formula which works well but is cut off at seven
arguments:

=IF(E3=301,"Phone Locked (PIN)",IF(E3=332,"Incorrect
Software",IF(E3=360,"Service Required",IF(E3=363,"Cannot Detect SIM
Card",IF(E3=371,"No Data Service",IF(E3=407,"Cosmetic Defect-Trackball",
IF(E3=414,"Debris/Fingerprints/Moisture Spots", IF(E3=415, "Display Lens
Scratched","N/A"))))))))

It's working very well, but I have about 35 different arguments I need to
enter.

Can anyone assist on a formula I can use? The current IF function works
great for what I need but I need more arguments.

Thanks!
Ryan Ford
 
Create a table than use vlookup to return the "If true statement"

Your Table in A1:B35
A B
301 Phone Locked (PIN)
332 Incorrect Software
363 Cannot Detect SIM Card

then your vlookup:
=vlookup(E3,A1:B15,2,False)
 
You can use VLOOKUP instead of multiple IFs. First of all, you need to
set up a table somewhere which lists your defect codes in one column
and the description in the next column, like this:

301 Phone Locked (PIN)
332 Incorrect Software
360 Service Required
363 Cannot Detect SIM Card
371 No Data Service
407 Cosmetic Defect-Trackball
414 Debris/Fingerprints/Moisture Spots
415 Display Lens Scratched

and so on. Suppose you put these in columns Y and Z. Then you can
replace your formula with this:

=VLOOKUP(E3,Y:Z,2,0)

Just add more codes to the bottom of your table as required.

Hope this helps.

Pete
 
If you use ENTIRE columns it may come back to haunt you.
=VLOOKUP(E3,Y:Z,2,0)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
You can use VLOOKUP instead of multiple IFs. First of all, you need to
set up a table somewhere which lists your defect codes in one column
and the description in the next column, like this:

301 Phone Locked (PIN)
332 Incorrect Software
360 Service Required
363 Cannot Detect SIM Card
371 No Data Service
407 Cosmetic Defect-Trackball
414 Debris/Fingerprints/Moisture Spots
415 Display Lens Scratched

and so on. Suppose you put these in columns Y and Z. Then you can
replace your formula with this:

=VLOOKUP(E3,Y:Z,2,0)

Just add more codes to the bottom of your table as required.

Hope this helps.

Pete
 
Thank you very much guys... I did look at the LOOKUP help pages but whoever
writes that stuff couldn't be more confusing! Thank you very much for all of
your help!
 
Back
Top