ExcelBanter - if and lookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - if and lookup (https://www.excelbanter.com/excel-worksheet-functions/6633-if-lookup.html)

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.
:confused:

--
Jettero2112
------------------------------------------------------------------------
Jettero2112's Profile: http://www.excelforum.com/member.php...o&userid=16622
View this thread: http://www.excelforum.com/showthread...hreadid=314875

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.
:confused:

--
Jettero2112
------------------------------------------------------------------------
Jettero2112's Profile:
http://www.excelforum.com/member.php...o&userid=16622
View this thread: http://www.excelforum.com/showthread...hreadid=314875

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.
:confused:

--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=314875