Carve out table

[OrangeYoda]

Good afternoon all.
I'm fairly familiar with Excel. I have nasty formula that works fine. I would like to see it shorter in order to maintain it in the future.

You can ignore most this formula. I've highlighted the issue. Each item below is one I have to measure by footage whereas all my other products are a simple count/quantity. So, in this formula I've built a carve out exception that looks at my per foot material(wire). If it sees a wire sku it reads a separate table (a table of wire) and brings back the cost per foot instead of per roll.

I need to add more wire types, but this is getting out of hand quickly. Is there a more efficient way to do this?

IF(ISNA(VLOOKUP(Q111,Pricebook!$A:$E,5,0)),"",IF(OR(Q111=2487703,Q111=2487704,Q111=2487705,Q111=2487710,Q111=2487708,Q111=2487712,Q111=852129,Q111=3571573,Q111=3571565,Q111=3571567,Q111=3571561,Q111=3571562,Q111=136847,Q111=136850),VLOOKUP(Q111,Price_Calc_Mod!$D:$H,5)*R111,VLOOKUP(Q111,Pricebook!$A:$E,5,0)*R111))

 

[OrangeYoda]

Good afternoon all.
I'm fairly familiar with Excel. I have nasty formula that works fine. I would like to see it shorter in order to maintain it in the future.

You can ignore most this formula. I've highlighted the issue. Each item below is one I have to measure by footage whereas all my other products are a simple count/quantity. So, in this formula I've built a carve out exception that looks at my per foot material(wire). If it sees a wire sku it reads a separate table (a table of wire) and brings back the cost per foot instead of per roll.

I need to add more wire types, but this is getting out of hand quickly. Is there a more efficient way to do this?

IF(ISNA(VLOOKUP(Q111,Pricebook!$A:$E,5,0)),"",IF(OR(Q111=2487703,Q111=2487704,Q111=2487705,Q111=2487710,Q111=2487708,Q111=2487712,Q111=852129,Q111=3571573,Q111=3571565,Q111=3571567,Q111=3571561,Q111=3571562,Q111=136847,Q111=136850),VLOOKUP(Q111,Price_Calc_Mod!$D:$H,5)*R111,VLOOKUP(Q111,Pricebook!$A:$E,5,0)*R111))

Even an Excel function I can google would be great. Just not sure what to Google at this point for help.

 

[OrangeYoda]

Bumping up since I posted this on a Friday afternoon. None of us are thinking Excel then.

 

[FormR]

Hi, instead of hard coding the product numbers in the formula - why not list then on a sheet. For example in column A of a sheet called exceptions.

...IF(ISNUMBER(MATCH(Q111,Exceptions!A:A,0)),VLOOKUP(Q111,Price_Calc_Mod!$D:$H,5)...

 

[OrangeYoda]

Hi, instead of hard coding the product numbers in the formula - why not list then on a sheet. For example in column A of a sheet called exceptions.

...IF(ISNUMBER(MATCH(Q111,Exceptions!A:A,0)),VLOOKUP(Q111,Price_Calc_Mod!$D:$H,5)...

I love you! That will work perfectly. ISNUMBER/MATCH just wasn't coming to the front of the brain. Ty!!!