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

1. ## 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!

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

Please check the announcement in yellow from above on how to upload a sample file.

3. ## Re: Looking up by 1 value, find row within range of values in 2 columns and return another

Here's an example attached, thank you.

4. ## Re: Looking up by 1 value, find row within range of values in 2 columns and return another

``Please Login or Register  to view this content.``
Regards.

5. ## Re: Looking up by 1 value, find row within range of values in 2 columns and return another

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/

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1