reverse vlookup and match formula

[sp1974]

I apologize if the header is not correct. I currently have a spreadsheet where I enter a part number and quantity and it uses a vlookup and match formula to pull a price from a 2nd tab that has various pricing brackets. My customer would like to enter a part number and price and have a quantity they need to order displayed. Currently I can not post the spreadsheet for confidentiality reasons but if you guys think it can be done I can remove info and post a dummy version for your help. Does anyone think this can be done? Also I do not want to change the format of the original spreadsheet because than there are 2 spreadsheets that will need to be maintained.

[Haseeb Avarakkan]

Hello SP,

Post your dummy file. Make sure to keep the layout.

[sp1974]

OK it will take me some time to remove sensitive info so I will post tomorrow

[Haseeb Avarakkan]

FYI,

You don't really have to post your original file. Just make a new dummy file with dummy data. Just make sure to keep the original LAYOUT. Also your expected outcome.

[sp1974]

Attached is the sample. The data to draw from is in tab 2. Currently I enter a part number in cells A7:A39 and the order qty for the corresponding part number in B7:B39 and the data in column c - I populate from tab 2. This works for me but now the customer has requested to be able to put in the part number and price and have the quantity that corresponds with that quantity populate. So for example they want to order part number 3 and their standard pricing (calculated off an average so it wont match exact to pricing on tab 2) might be $35.70. So in this case they would need to order 15,000 @ $33.90 in order to meet or be less than their standard pricing. Is there a way to make this happen?

[Haseeb Avarakkan]

Try this on B7, then copy down.

Please Login or Register  to view this content.

[sp1974]

Perfect! Thanks!

[sp1974]

It seems like the formula works only if all the pricing brackets are populated. Se the attached copy of the sample with the formula inserted. If all price brackets are not populated it will not populate the first two and than as it reaches the end of the pricing brackets it will also quit populating. Any advise on how to fix this?

[sp1974]

By the way, I flip flopped columns B and C so the above formula where it references C7 it is actually B7 and the formula is in column C not B. Thanks