if and lookup

[Jettero2112]

I wrote a formula to be able to change a part number based on the colo
and length of a part. The color cell (always located at K14) and th
part length (E16 below but changes) in the formula change when I cop
to a new cell. How can I get it to change only the part length cel
but not the color cell when I copy to a new cell? (hope that's not to
confusing)

=IF((K14="TUDOR BROWN"),VLOOKUP(E16,TBROWN,2),IF((K14="SPANIS
CLAY"),VLOOKUP(E16,SCLAY,2),IF((K14="OCEA
BLUE"),VLOOKUP(E16,OBLUE,2),IF((K14="SLAT
GRAY"),VLOOKUP(E16,SGRAY,2),IF((K14="REGA
RED"),VLOOKUP(E16,RRED,2),IF((K14="CLASSI
GREEN"),VLOOKUP(E16,CGREEN,2)))))))

Thanks.
:confused

 

[N Harkawat]

Use $K$14 instead of K14

 

[Don Guillett]

make k14 absolute. Highlight and use f4

 

[Domenic]

Try the following...

=vlookup(E16,choose(match($K$14,{"TUDOR BROWN","SPANISH CLAY","OCEA
BLUE","SLATE GRAY","REGAL RED","CLASSI
GREEN"},0),TBROWN,SCLAY,OBLUE,SGRAY,RRED,CGREEN),2,0)

OR

=vlookup(E16,choose(match($K$14,Sheet2!A1:A6,0),TBROWN,SCLAY,OBLUE,SGRAY,RRED,CGREEN),2,0)

...where Sheet2!A1:A6 contains the following list...

TUDOR BROWN
SPANISH CLAY
OCEAN BLUE
SLATE GRAY
REGAL RED
CLASSIC GREEN

Hope this helps!