+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    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. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,096

    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.
    Click the * to say thanks.

  3. #3
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    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.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    659

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

    Please try

    Please Login or Register  to view this content.
    Regards.
    My English is very poor, so please be patient >_<"

  5. #5
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    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/

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return values to specific columns based on range of cell values
    By sbrt10 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-27-2019, 03:15 AM
  2. Find smallest value in range and return adjacent values
    By bunchie3174 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-24-2016, 06:09 AM
  3. VB to find recurring values and return x rows and x columns from result
    By TravisJB in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 03-04-2013, 02:01 PM
  4. How to find closest values in multiple columns and return adjacent values.
    By Patrician in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 01:47 PM
  5. Replies: 1
    Last Post: 11-02-2012, 08:32 AM
  6. [SOLVED] Using range.find to find two specific values in two columns
    By mhrub in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2012, 01:02 PM
  7. Replies: 3
    Last Post: 04-10-2012, 01:51 PM

Bookmarks

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