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

[premis]

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?

[FlameRetired]

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.

[premis]

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

[MrShorty]

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?

[FlameRetired]

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

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.

[premis]

Thank you so much for the help!

[JohnTopley]

Non-array version

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

[JohnTopley]

Duplicate post

[FlameRetired]

@ JohnTopley

Yeah. Better.