if function only lets me do 8 ifs

[d0wnt0wn]

ok here is my scenario.... i want to make a drop down menu for all of my
material on an estimate sheet... if i click on one of the materials on
the menu i want the 4 columns of info that are related to that product
to appear where my drop down is .. ie... material, price, price with
tax, price per square foot... some other people tried to help me witht
his earlier but one way didnt really apply to me and the other was too
advanced for me to understand so i tried this using the if function and
i got something like this..

first i made a table on the same sheet with all the 4 columns lets say
they start at a100-d100 ( my dropdown is starting at a2-d2)

so my formula goes like this for b2
=if(a2=a100,b100,if(a2=a101,b101,if(a2=a102,b102....ect and likewise
for c and d column.

but i run out of functions after 8 ifs....

i have about 50 materials in my list so how can i achieve this???? any
help on this would be greatly appreciated.

 

[Don Guillett]

use a lookup table

 

[Ken Wright]

From your text it sounds like you already have all your data in A2100, and are
simply looking to use a dropdown on that data in Col A and have all the data for
that entry in B C D appear as well. Well that is exactly what it will do anyway
if you are using Data / Filter / Autofilter:-

http://www.contextures.com/xlautofilter01.html

If however, you have your dropdown in another place or on another sheet, then
you would use VLOOKUP to get what you needed. Assume you have your table on
sheet2 in A1100, name that range mytable. Now also name the range A1:A100
mylist.

In any cell, but say A1 on sheet1, Do Data / Validation / List / (Now just type
=mylist in the box and hit OK)
In cell B1 put the following formula:-

=VLOOKUP($A$1,mytable,2,0)

In C1 =VLOOKUP($A$1,mytable,3,0)
In D1 =VLOOKUP($A$1,mytable,4,0)

You can now click on the dropdwon in cell A1, and whatever you choose, the
associated data from sheet 2 will appear in cells B11 on sheet1.

 

[Don Guillett]

After making the table use
=vlookup(what to look up, lookup table range, column,false)

 

[d0wnt0wn]

thanks very much you guys but especially ken wright... your info was
totally helpful to me and what i was trying to do

 

[Don Guillett]

That's why he gets the MVP