Combining IF/Index/Match over two sheets with multiple criteria

[winter8806]

Hello everyone,
I am trying to get a formula that will pull a price based on two pieces of criteria, one being the product and the other quantity. For example, I have a part number of 880 (A15) and a quantity of 15(G15), I need the price returned (S15) based on these two pieces of criteria from sheet 2. But the quantities are listed for price as 1-35 ('Sheet 2'!G), 36-71('Sheet 2'!H), so I need it to look in both columns to pull the correct price.

Sheet 2
[TABLE="width: 411"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]C
Part Number[/TD]
[TD]G
1 TO 35[/TD]
[TD]H
36-71[/TD]
[/TR]
[TR]
[TD]880[/TD]
[TD]6.30[/TD]
[TD]5.99[/TD]
[/TR]
</tbody>[/TABLE]

The formula that I currently have that comes back as #N/A or depending on how i adjust it #REF

=(INDEX('Sheet 2'!G:G,MATCH(1,IF(AND(('Sheet 2'!C:C='Sheet 1'!A15)*('Sheet 1'!G15<=35)),'Sheet 2'!G:G,0)),INDEX('Sheet 2'!H:H,MATCH(1,IF(AND(('Sheet2'!C:C=A15)*(AND('Sheet 1'!G15>=36,'Sheet 1'!G15<=71))),'Sheet 2'!H:H,0)))))

I feel like it's something simple to fix but I think I have been looking at it to long for my mind to grasp the easy answer.

Thanks!

 

[Special-K99]

Here's my first thought.

When you create ANYTHING make sure everything conforms to a standard - AND STICK TO IT.
If you want "1 TO 35" then use "36 TO 71" not "36-71".
That way you may be able to use one fairly short formula.

Try

in S15
=INDEX(Sheet 2!G$1:H$1000,MATCH(A$15,C$1:C$1000,0),IF(G$15<=35,1,2))

Dont use full columns, e.g. G:G, things will be quicker if you limit the range by including a row number, e.g. G1:G1000

 

[winter8806]

Here's my first thought.

When you create ANYTHING make sure everything conforms to a standard - AND STICK TO IT.
If you want "1 TO 35" then use "36 TO 71" not "36-71".
That way you may be able to use one fairly short formula.

Try

in S15
=INDEX(Sheet 2!G$1:H$1000,MATCH(A$15,C$1:C$1000,0),IF(G$15<=35,1,2))

Dont use full columns, e.g. G:G, things will be quicker if you limit the range by including a row number, e.g. G1:G1000

Thank you, I was give the spreadsheet and told to make it work.

If we were to add another column for more pricing options by quantity say 72-144 how would I take this into consideration?

 

[Special-K99]

1. Make sure all the headings contain a hyphen
2. In the row below copy this formula across the row for each column you have

=LEFT(G1,FIND("-",G1)-1)+0

This will list the first number in each range.

Then use this
=INDEX(Sheet 2!G$1:ZZ$1000,MATCH(A$15,C$1:C$1000,0),MATCH(G15,G2:ZZ2,1))