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

	ColumnA	ColumnB	ColumnC	ColumnD
Row1	Product	Max	Min	Average
Row2	productA	$2.00	$1.00	$1.50
Row3	productB	$5.00	$1.00	$3.00
Row4	ProductC	$10.00	$20.00	$15.00

<tbody>
</tbody>

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.

	ColumnF	ColumnG	ColumnH
Row6	Product	Price	Price New (adjusted based on above) - this is where i need to adjust my formula
Row7	productA	$1.05	$1.05
Row8	productA	$3.00	$1.50
Row9	productB	$2.75	$2.75
Row10	productC	$5.00	$15.00

<tbody>
</tbody>

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;">

ColumnA	ColumnB	ColumnC	ColumnD
Row1	Product	Max	Min	Average
Row2	productA	$2.00	$1.00	$1.50
Row3	productB	$5.00	$1.00	$3.00
Row4	ProductC	$10.00	$20.00	$15.00

<tbody>
</tbody>

</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:

	A	B	C	D
1	Product	Max	Min	Average
2	productA	$2.00	$1.00	$1.50
3	productB	$5.00	$1.00	$3.00
4	ProductC	$10.00	$20.00	$15.00

<tbody>
</tbody>

Prices worksheet:

	A	B	C	D	E
1	Product	Price	Price New 1	Price New 2	Match
2	productA	$1.05	$1.05	1.05	1
3	productA	$3.00	$1.50	1.5	1
4	productB	$2.75	$2.75	2.75	2
5	productC	$5.00	$15.00	15	3

<tbody>
</tbody>

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.