Home |
Search |
Today's Posts |
#1
|
|||
|
|||
if and lookup
I wrote a formula to be able to change a part number based on the color and length of a part. The color cell (always located at K14) and the part length (E16 below but changes) in the formula change when I copy to a new cell. How can I get it to change only the part length cell but not the color cell when I copy to a new cell? (hope that's not too confusing) =IF((K14="TUDOR BROWN"),VLOOKUP(E16,TBROWN,2),IF((K14="SPANISH CLAY"),VLOOKUP(E16,SCLAY,2),IF((K14="OCEAN BLUE"),VLOOKUP(E16,OBLUE,2),IF((K14="SLATE GRAY"),VLOOKUP(E16,SGRAY,2),IF((K14="REGAL RED"),VLOOKUP(E16,RRED,2),IF((K14="CLASSIC GREEN"),VLOOKUP(E16,CGREEN,2))))))) Thanks. -- Jettero2112 ------------------------------------------------------------------------ Jettero2112's Profile: http://www.excelforum.com/member.php...o&userid=16622 View this thread: http://www.excelforum.com/showthread...hreadid=314875 |
#2
|
|||
|
|||
Use $K$14 instead of K14
"Jettero2112" wrote in message ... I wrote a formula to be able to change a part number based on the color and length of a part. The color cell (always located at K14) and the part length (E16 below but changes) in the formula change when I copy to a new cell. How can I get it to change only the part length cell but not the color cell when I copy to a new cell? (hope that's not too confusing) =IF((K14="TUDOR BROWN"),VLOOKUP(E16,TBROWN,2),IF((K14="SPANISH CLAY"),VLOOKUP(E16,SCLAY,2),IF((K14="OCEAN BLUE"),VLOOKUP(E16,OBLUE,2),IF((K14="SLATE GRAY"),VLOOKUP(E16,SGRAY,2),IF((K14="REGAL RED"),VLOOKUP(E16,RRED,2),IF((K14="CLASSIC GREEN"),VLOOKUP(E16,CGREEN,2))))))) Thanks. -- Jettero2112 ------------------------------------------------------------------------ Jettero2112's Profile: http://www.excelforum.com/member.php...o&userid=16622 View this thread: http://www.excelforum.com/showthread...hreadid=314875 |
#4
|
|||
|
|||
Try the following... =vlookup(E16,choose(match($K$14,{"TUDOR BROWN","SPANISH CLAY","OCEAN BLUE","SLATE GRAY","REGAL RED","CLASSIC GREEN"},0),TBROWN,SCLAY,OBLUE,SGRAY,RRED,CGREEN),2 ,0) OR =vlookup(E16,choose(match($K$14,Sheet2!A1:A6,0),TB ROWN,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! Jettero2112 Wrote: I wrote a formula to be able to change a part number based on the color and length of a part. The color cell (always located at K14) and the part length (E16 below but changes) in the formula change when I copy to a new cell. How can I get it to change only the part length cell but not the color cell when I copy to a new cell? (hope that's not too confusing) =IF((K14="TUDOR BROWN"),VLOOKUP(E16,TBROWN,2),IF((K14="SPANISH CLAY"),VLOOKUP(E16,SCLAY,2),IF((K14="OCEAN BLUE"),VLOOKUP(E16,OBLUE,2),IF((K14="SLATE GRAY"),VLOOKUP(E16,SGRAY,2),IF((K14="REGAL RED"),VLOOKUP(E16,RRED,2),IF((K14="CLASSIC GREEN"),VLOOKUP(E16,CGREEN,2))))))) Thanks. -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=314875 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
need check two worksheets to lookup a value | Excel Discussion (Misc queries) | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
Another Lookup function, please | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |