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

dayday123

New Member
Joined
Mar 12, 2014
Messages
24
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>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top