|
Question About IF Function and LOOKUP
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 |
Question About IF Function and LOOKUP
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) -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Ryan F" wrote: 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 |
Question About IF Function and LOOKUP
|
Question About IF Function and LOOKUP
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 On Jan 20, 3:14*pm, Ryan F <Ryan wrote: 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 |
Question About IF Function and LOOKUP
|
Question About IF Function and LOOKUP
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! "Don Guillett" wrote: 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 "Pete_UK" wrote in message ... 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 On Jan 20, 3:14 pm, Ryan F <Ryan wrote: 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 |
| All times are GMT +1. The time now is 07:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com