index match? index match match? what should i use here?

[dayday123]

Hi All -

I currently have a very long and complicated formula that i am positive can be made more simple but just not sure exactly how to accomplish it. I have a table of products, max price, min price, and average as shown below in one tab of an excel

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[/TR]
[TR]
[TD]Row1[/TD]
[TD]Product[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]productA[/TD]
[TD]$2.00[/TD]
[TD]$1.00[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]productB[/TD]
[TD]$5.00[/TD]
[TD]$1.00[/TD]
[TD]$3.00[/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD]ProductC[/TD]
[TD]$10.00[/TD]
[TD]$20.00[/TD]
[TD]$15.00[/TD]
[/TR]
</tbody>[/TABLE]

In another tab I have products and prices listed that may fall within the range or without. If it falls within the range for that specific product then i want to use the price shown. If it falls outside of the range then i want to use the average.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnF[/TD]
[TD]ColumnG[/TD]
[TD]ColumnH[/TD]
[/TR]
[TR]
[TD]Row6[/TD]
[TD]Product [/TD]
[TD]Price[/TD]
[TD]Price New (adjusted based on above) - this is where i need to adjust my formula[/TD]
[/TR]
[TR]
[TD]Row7[/TD]
[TD]productA[/TD]
[TD]$1.05[/TD]
[TD]$1.05[/TD]
[/TR]
[TR]
[TD]Row8[/TD]
[TD]productA[/TD]
[TD]$3.00[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]Row9[/TD]
[TD]productB[/TD]
[TD]$2.75[/TD]
[TD]$2.75[/TD]
[/TR]
[TR]
[TD]Row10[/TD]
[TD]productC[/TD]
[TD]$5.00[/TD]
[TD]$15.00[/TD]
[/TR]
</tbody>[/TABLE]


Can someone help me with this or point me in the right direction of what type of formula i shoud use?

THanks so much!
****** id="cke_pastebin" style="position: absolute; top: -3.8147e-06px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[/TR]
[TR]
[TD]Row1[/TD]
[TD]Product[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]productA[/TD]
[TD]$2.00[/TD]
[TD]$1.00[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]productB[/TD]
[TD]$5.00[/TD]
[TD]$1.00[/TD]
[TD]$3.00[/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD]ProductC[/TD]
[TD]$10.00[/TD]
[TD]$20.00[/TD]
[TD]$15.00[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[mole999]

so what do you have as a formula, useful to see what you currently do. Also you seem to be using Chrome, and the editor in that causes some pasting issues you'll see i your post the ****** which is the removal of bad layout code

 

[dayday123]

currently i am just using a ton of nested if functions which is annoying because every time we add a new product i have to go in and edit the formula because each if function refers to a specific product (specific row of information). i want it to just be a vlookup of sorts where i can reference the entire columns so as new products are added it automatically gets included.

Also the nested if functions wont work soon because apparently there is a character limit on formuals?? i just came across this in excel today.

yes i see what you mean about the **** but in the post i still see the tables so not sure what it removed. It looks right to me.

 

[dayday123]

currently i am just using a ton of nested if functions which is annoying because every time we add a new product i have to go in and edit the formula because each if function refers to a specific product (specific row of information). i want it to just be a vlookup of sorts where i can reference the entire columns so as new products are added it automatically gets included.

Also the nested if functions wont work soon because apparently there is a character limit on formulas?? i just came across this in excel today.

Do you have any thoughts on what i should use?

yes i see what you mean about the **** but in the post i still see the tables so not sure what it removed. It looks right to me.

 

[thisoldman]

Products worksheet:

<tbody>
[TD="class: xl66"][/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl67"]B[/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl67"]D[/TD]

[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Product[/TD]
[TD="class: xl67"]Max[/TD]
[TD="class: xl67"]Min[/TD]
[TD="class: xl67"]Average[/TD]

[TD="class: xl64"]2[/TD]
[TD="class: xl64"]productA[/TD]
[TD="class: xl65, align: right"]$2.00[/TD]
[TD="class: xl65, align: right"]$1.00[/TD]
[TD="class: xl65, align: right"]$1.50[/TD]

[TD="class: xl64"]3[/TD]
[TD="class: xl64"]productB[/TD]
[TD="class: xl65, align: right"]$5.00[/TD]
[TD="class: xl65, align: right"]$1.00[/TD]
[TD="class: xl65, align: right"]$3.00[/TD]

[TD="class: xl64"]4[/TD]
[TD="class: xl64"]ProductC[/TD]
[TD="class: xl65, align: right"]$10.00[/TD]
[TD="class: xl65, align: right"]$20.00[/TD]
[TD="class: xl65, align: right"]$15.00[/TD]

</tbody>

Prices worksheet:
[TABLE="class: grid, width: 334"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Price[/TD]
[TD]Price New 1[/TD]
[TD]Price New 2[/TD]
[TD]Match[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]productA[/TD]
[TD="align: right"]$1.05[/TD]
[TD="align: right"]$1.05[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]productA[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$1.50[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]productB[/TD]
[TD="align: right"]$2.75[/TD]
[TD="align: right"]$2.75[/TD]
[TD="align: right"]2.75[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]productC[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

In Prices!C2 and copied downward:
=IF(OR(B2>INDEX(Products!$B$2:$B$4, MATCH(A2, Products!$A$2:$A$4, 0)),
B2< INDEX(Products!$C$2:$C$4, MATCH(A2, Products!$A$2:$A$4, 0))),
INDEX(Products!$D$2:$D$4, MATCH(A2, Products!$A$2:$A$4, 0)), B2)


If you can afford a helper column, you can put the MATCH formula there. The Match only needs to be done once per row.

In Prices!E2 and copied downward:
=MATCH(A2, Products!$A$2:$A$4, 0)

Then in Prices!D2 and copied downward, I have:
=IF(OR(B2>INDEX(Products!$B$2:$B$4,E2),
B2< INDEX(Products!$C$2:$C$4,E2)),
INDEX(Products!$D$2:$D$4, E2),B2)

 

[dayday123]

this worked beautifully!! thank you so much!!!

 

[thisoldman]

That's nice to know it worked for you. I'm happy to have helped.