"=IF" with more than 64 levels of nesting?

[Jihad Akoury]

I am using excel to make sheet with drop list where the price of the corresponding item appears in a specified cell. However there's more than 64 items and i can't add more "IF".

Here's the formula:

=IF(B4=M!$A$1,"35",IF(B4=M!$A$2,"50",IF(B4=M!$A$3,"70",IF(B4=M!$A$4,"80",IF(B4=M!$A$5,"85",IF(B4=M!$A$6,"90",IF(B4=M!$A$7,"50",IF(B4=M!$A$8,"50",IF(B4=M!$A$9,"75",IF(B4=M!$A$10,"75",IF(B4=M!$A$11,"80",IF(B4=M!$A$12,"100",IF(B4=M!$A$13,"150",IF(B4=M!$A$14,"105",IF(B4=M!$A$15,"150",IF(B4=M!$A$16,"50",IF(B4=M!$A$17,"100",IF(B4=M!$A$18,"125",IF(B4=M!$A$19,"150",IF(B4=M!$A$20,"100",IF(B4=M!$A$21,"125",IF(B4=M!$A$22,"150",IF(B4=M!$A$23,"300",IF(B4=M!$A$24,"55",IF(B4=M!$A$25,"50",IF(B4=M!$A$26,"100",IF(B4=M!$A$27,"100",IF(B4=M!$A$28,"25",IF(B4=M!$A$29,"30",IF(B4=M!$A$30,"300",IF(B4=M!$A$31,"350",IF(B4=M!$A$32,"375",IF(B4=M!$A$33,"225",IF(B4=M!$A$34,"450",IF(B4=M!$A$35,"480",IF(B4=M!$A$36,"520",IF(B4=M!$A$37,"350",IF(B4=M!$A$38,"600",IF(B4=M!$A$39,"500",IF(B4=M!$A$40,"600",IF(B4=M!$A$41,"35",IF(B4=M!$A$42,"50",IF(B4=M!$A$43,"60",IF(B4=M!$A$44,"30",IF(B4=M!$A$45,"100",IF(B4=M!$A$46,"50",IF(B4=M!$A$47,"150",IF(B4=M!$A$48,"100",IF(B4=M!$A$49,"150",IF(B4=M!$A$50,"100",IF(B4=M!$A$51,"200",IF(B4=M!$A$52,"150",IF(B4=M!$A$53,"500",IF(B4=M!$A$54,"400",IF(B4=M!$A$55,"600",IF(B4=M!$A$56,"55",IF(B4=M!$A$57,"85",IF(B4=M!$A$58,"100",IF(B4=M!$A$59,"125",IF(B4=M!$A$60,"150",IF(B4=M!$A$61,"250",IF(B4=M!$A$62,"300",IF(B4=M!$A$63,"110",IF(B4=M!$A$64,"130","0"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

How could i could i extend the list?

Thank you in advance!

 

[Jonmo1]

I would add an extra column to put the corresponding values to each value of A1:A64
So say in B1:B64, put the corresponding values to the column A values (35,50,70,80 etc..)

Then use
=VLOOKUP(B4,M!$A$1:$B$64,2,FALSE)

 

[Jihad Akoury]

I would add an extra column to put the corresponding values to each value of A1:A64
So say in B1:B64, put the corresponding values to the column A values (35,50,70,80 etc..)

Then use
=VLOOKUP(B4,M!$A$1:$B$64,2,FALSE)

WOOW thank you a lot dear, you saved my life!!

 

[Jihad Akoury]

Oh one more question, what should i do in order to make the default number in the cell to be 0 if no option is selected??

 

[Jonmo1]

Try
=IFERROR(VLOOKUP(B4,M!$A$1:$B$64,2,FALSE),0)