+ Reply to Thread
Results 1 to 4 of 4

[Formula] Lookup based on two range values

  1. #1
    Registered User
    Join Date
    11-09-2012
    Location
    seattle
    MS-Off Ver
    Excel 2007
    Posts
    42

    [Formula] Lookup based on two range values

    Example.png

    Hi, I got a semi hard one today :D

    I have two columns with amounts and percentages and a third column for my return values.

    I have a chart with a header range of:

    0-60.00
    60.01-65.00
    65.01-70.00
    70.01-75.00
    75.01-80.00
    80.01-85.01

    ..and header row of:

    10000-34999
    35000-49999
    50000-74999
    75000-99999
    100000-150000
    150001-350000
    350001-500000
    500001>

    What I want to do is have a formula identify the amount in cell A2 and percentage in B2 and find the intersection value in the two ranges.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,899

    Re: [Formula] Lookup based on two range values

    Try this in C2:

    =INDEX(F$3:L$10,MATCH(A2,E$3:E$10,1),MATCH(B2,F$2:L$2,1))

  3. #3
    Registered User
    Join Date
    11-09-2012
    Location
    seattle
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: [Formula] Lookup based on two range values

    Quote Originally Posted by 63falcondude View Post
    Try this in C2:

    =INDEX(F$3:L$10,MATCH(A2,E$3:E$10,1),MATCH(B2,F$2:L$2,1))
    Thank you so much! You rock!

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,899

    Re: [Formula] Lookup based on two range values

    You're welcome. Happy to help.

    Thanks for the rep!

+ 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. Lookup maximum corresponding value based on range of reference values
    By wlade122 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-06-2016, 05:59 PM
  2. [SOLVED] Lookup in a range of cells and extracting cells based on values and criteria
    By A_W in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-03-2016, 05:34 AM
  3. [SOLVED] need formula to get unique values through lookup from adjacent column extent range
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-08-2016, 02:59 AM
  4. Formula or Macro to lookup values in closed spreadsheet based on description. . .
    By dara3273 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2015, 04:31 PM
  5. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  6. Replies: 4
    Last Post: 04-21-2011, 02:59 PM
  7. How to lookup values between a delimiter and replace based on range?
    By cda in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-06-2011, 04:34 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