+ Reply to Thread
Results 1 to 9 of 9

How to lookup a value in a table using a range in the lookup columns

  1. #1
    Registered User
    Join Date
    07-17-2013
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2007
    Posts
    19

    How to lookup a value in a table using a range in the lookup columns

    I am trying to reference a table to pull a value. The table X and Y axis require me to find a range for each row and column. Is there a formula that?

    I'll try to explain it better.

    Here is my table
    chart.jpg

    A1=loss ratio
    A2=Premium Growth
    A3=Result

    So, if A1=46% and A2= 109% I need A3 to return 1.6% from that chart.

    Can this be done with a formula?

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,625

    Re: How to lookup a value in a table using a range in the lookup columns

    Could you upload an Excel workbook with what you have above? It saves us having to retype data and it makes it easier to suggest / demonstrate modifications to the lookup limits.

    If you are not familiar with how to do this:
    • click FAQ at the top of this page,
    • under Board FAQ click Reading and posting messages
    • then click Attachments and images
    • You will find instructions on how to do this.
    Dave

  3. #3
    Registered User
    Join Date
    07-17-2013
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: How to lookup a value in a table using a range in the lookup columns

    Thanks FlameRetired,

    Attached is the book I am working in. I need the value from the table to be in the highlighted cell G4

    Book1.xlsx

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,235

    Re: How to lookup a value in a table using a range in the lookup columns

    Are you familiar with Excel's lookup functions -- in particular the MATCH() and INDEX() functions? (https://support.office.com/en-us/art...__toc309306714 ). This should be relatively straightforward with these functions:

    1) A MATCH() function to locate the column # based on the loss ratio. Note that the 3rd argument of the MATCH() function will be -1, since your tabulated loss ratios are in descending order left to right.
    2) A second MATCH() function to locate the row # based on Premium growth. Note that, since the tabulated premium growths are ascending top to bottom, the 3rd argument to the MATCH() function will be 1.
    3) An INDEX() function to take the results of the two MATCH() functions and return the corresponding cell in the table.

    What part of that do you get stuck on?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,625

    Re: How to lookup a value in a table using a range in the lookup columns

    premis,

    I took the liberty of changing the boundaries in the column and row headers of the attached. These can

    of course be changed to whatever you prefer. This is why. The lower boundary in Q6 is 89.1% and the

    upper boundary in R5 (row above) is 89.0%. Should you designate say 89.05% there is no range that fits

    into. So I changed R5 to 89.1% and did likewise with R6:R13. The same holds true for the column

    headers in rows 3 and 4. This way formulae can refer to upper boundaries as < upper and >= to lower.

    The formula array entered is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you arenít familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    The file is attached.

  6. #6
    Registered User
    Join Date
    07-17-2013
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: How to lookup a value in a table using a range in the lookup columns

    Thank you so much for the help!

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: How to lookup a value in a table using a range in the lookup columns

    Non-array version

    =INDEX($S$5:$AA$14,MATCH($D$4,$Q$5:$Q$14,1),MATCH($F$4,$S$3:$AA$3,-1))

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: How to lookup a value in a table using a range in the lookup columns

    Duplicate post

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,625

    Re: How to lookup a value in a table using a range in the lookup columns

    @ JohnTopley

    Yeah. Better.

+ 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. [SOLVED] VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)
    By Ebalinska in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 01-14-2016, 05:55 AM
  2. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  3. Lookup a blank cell in a Lookup table and return a figure.
    By jonnops in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2014, 07:33 AM
  4. [SOLVED] Multiple lookup adding columns, then multiplying, then lookup, add, and subtract
    By mamig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-08-2013, 12:55 AM
  5. [SOLVED] Lookup question matching part of lookup value in the table array
    By kosmo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2013, 11:07 AM
  6. Replies: 7
    Last Post: 06-19-2011, 12:51 PM
  7. Get Range to Lookup from a Lookup table
    By JG Scott in forum Excel General
    Replies: 2
    Last Post: 10-15-2005, 10:05 AM

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