winter8806
New Member
- Joined
- Jun 28, 2018
- Messages
- 6
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
<colgroup><col><col><col></colgroup><tbody>
</tbody>
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!
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
C Part Number | G 1 TO 35 | H 36-71 |
880 | 6.30 | 5.99 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
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!