Looking up by 1 value, find row within range of values in 2 columns and return another

Hey all - here's what I'm trying to solve below. I guess what's hanging me up is I need to reference sheet 2 that has many different product tiers and based on a unique value it needs to reference those specific lines to determine tier. I'm seeing many examples of tiered pricing with one set of rules but I need it dynamic based on the unique value of each row.

Sheet 1:
Column A = unique value to identify all tier pricing within Sheet 2
Column B = quantity to determine which specific tier to return from Sheet 2

Sheet 2:
Column A = unique value that matches Sheet 1
Column B = starting unit of tier quantity
Column C = ending unit of tier quantity
Column D = tier price to be returned to Sheet 1

Tier example:
Product tier 1: 1 through 599 quantity - $125
Product tier 2: 600 through 1250 quantity - $200
Product tier 3: 1251 through 2000 quantity - $300
* all 3 rows above have same unique value matching Sheet 1

Thank you in advance I really appreciate it!

Here's an example attached, thank you.

This is perfect, thank you.

I had found something using tables that was working but it's very process intensive on hundreds of thousands of lines. Super glad you provided a non-table solution. If the sumifs above doesn't work for someone reading this later try: https://chandoo.org/wp/pricing-tier-lookup-formula/