Dynamically change a cell value based on a range of over values

Hello everyone, I'm building a pricing tool that should generate a different price level based on 300 ranges of values.

I'm entering a discreet value in a specific cell reserved for manual data entry.

Based on the value manually entered in that cell, I want a separate cell to populate with a corresponding value based on which range of values that discreet value falls within.

If I enter 250,000 manually, the other cell should look up in which of the 300 ranges that value exists, and populate with another value on the same line. I have 300 such lines and the variables could fall in any of these 300 ranges.

I've tried IF(AND statements and tried to define my value ranges as "=IF(AND(D5>=250000,D5<=300000),IF(AND...), and that works for a single range; but I don't think I can get away with this approach on 300 different range definitions within a single cell.

How do I solve this?

Thank you all in advance for your help!

I think it was just a matter of placing your $ signs. Try this in G6 then you can copy it down and across as far as you need.

=IF(AND(G$2>=$B6,G$2<=$C6),$D6,0)

Thank you for taking the time Gregb11 !!

Your modification is definitely correct in this narrow definition. However, it doesn't go far enough in what I'm attempting to do.

I need the spreadsheet to apply the rule across all 300 ranges of values. So whatever the discreet value is entered above in line 2, it will check all 300 of the value ranges, not just the one, and apply the corresponding value in column D.

I hope this makes sense!

Hello AIIGW, and thank you for the uncanny insight into my intent for this formula! I was not educated on the INDEX and MTCH functions. I need to take an Excel course - you've clearly mastered it!

Hello, I'm still working through adapting the solution to the rest of my spreadsheet and I'm formulating some additional questions. Should I mark this thread as solved now and start a new one? Or should I keep this one open until complete.

BTW, I'm not sure why you don't think the formula I gave in Post #2 above won't handle 300 ranges of values. It handles as many as you want to add. It gives the same answer as Ali's, just a different formula (it puts zeros instead of empty, but you could change the end to end in "" instead of 0)

Hello Greg, thank you for taking the time - you are correct and your approach could also serve this purpose - I appreciate it!

Hello Ali, thank you so much for your expert input - I was able to complete my spreadsheet to satisfaction. I marked the thread as solved.