Function to pull table data by two parameters

8thwondurrrr

New Member
Joined
Sep 22, 2015
Messages
13
Hello,
I am looking for help creating a tracker for commissions earned, first looking up premium % to goal in a Column and then returning the cell input for a corresponding row for the policy% count to goal. I had the attached table starting in Cell q1, and the formula to find the multiplier (table data) is as follows
=(vlookup(e3,r4:y10,hlookup(e2,s2:y3,2,false),false)).
in this function e3 is where the tracker is calculating the sold premiums % to goal and e2 is the % to count goals.

As an idea, on if you made 100% to goal for premium and count, the function should return .35% multiplier.

We need the function to be able to work inside a range of percentages. Any Ideas???

Thank you in advance!
 

Attachments

  • SnipImage.JPG
    SnipImage.JPG
    38.8 KB · Views: 2

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can you share some sample data? Preferably using XL2BB. Makes it easier to help you.
At first glance, I would use an INDEX-Match-Match combo.
Maybe the % goals on top and the left could be Bins (80% , 90%, 100%, ...) as it is easier to find approximate matches.
Using a match("Premium", RangeOfGoals,0) + match("%, RangeOf%,0) enables finding the correct % over multiple partitions inside a single "table".
 

8thwondurrrr

New Member
Joined
Sep 22, 2015
Messages
13
I apologize for whatever the reason the add in isn't working I'll try to provide some additional details.
In the example, we have 100% to Premium and 100% to count, so the multiplier should be 1.40, meaning my formula should calculate $1,152.354 ($82,311 * 1.40) but instead it is calculating $946 (Looks like it is multiplying $82,311 * 1.15%).

Here is my Formula:
=D19*(VLOOKUP(E18,C23:J29,HLOOKUP(E17,D21:J22,2,FALSE),FALSE))

I attached the screenshot for reference.
 

Attachments

  • HELP!.png
    HELP!.png
    45.3 KB · Views: 4

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,300
Office Version
  1. 2016
Platform
  1. Windows
Hi 8thwondurrrr,

Your HLOOKUP returns 4 and as your VLOOKUP starts in column C it will retrieve F26.

You will have other challenges as your first and last boundaries horizontally and vertically start "<" or ">" so the cell is treated as text, which is why it left aligns by default.
 

8thwondurrrr

New Member
Joined
Sep 22, 2015
Messages
13

ADVERTISEMENT

Thank you for the help, even when I remove the less than or greater than symbols remove it still doesn't calculate correctly.
Also, how can I make the spreadsheet use the correct columns and rows when the % to goal is not an exact match. IE if you are at lets say 99% it will use the 90% column/row?
 

Attachments

  • HELP!.png
    HELP!.png
    38.4 KB · Views: 1

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,300
Office Version
  1. 2016
Platform
  1. Windows
Does this do what you want?

8thwondurrrr.xlsx
BCDEFGHIJ
17Y HLOOKUP100%
18X VLOOKUP100%
19Premium sold82311PAID1152.354
20Policty2goal
21Premium payout factor0%80%90%100%110%120%130%
221234567
230%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
2480%0.00%0.80%0.85%0.90%0.95%1.00%1.05%
2590%0.00%0.90%0.95%1.00%1.05%1.10%1.15%
26100%0.00%1.10%1.15%1.40%1.45%1.50%1.55%
27110%0.00%1.30%1.35%1.70%1.75%1.80%1.85%
28120%0.00%1.50%1.55%2.05%2.10%2.15%2.20%
29130%0.00%1.70%1.75%2.35%2.40%2.50%2.75%
Sheet1
Cell Formulas
RangeFormula
H19H19=D19*(VLOOKUP(E18,C23:J29,1+HLOOKUP(E17,D21:J22,2,TRUE),TRUE))
 

8thwondurrrr

New Member
Joined
Sep 22, 2015
Messages
13

ADVERTISEMENT

Yes! Adding the 1+ works, so does changing row H to start at 2 (instead of 1). Great thank you!

How can I make it to where the it works on a range of cells instead of only the percent's in row 21? IE it will work is we are at lets say 80%, but not 81% or 82%.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,300
Office Version
  1. 2016
Platform
  1. Windows
Yes! Adding the 1+ works, so does changing row H to start at 2 (instead of 1). Great thank you!

How can I make it to where the it works on a range of cells instead of only the percent's in row 21? IE it will work is we are at lets say 80%, but not 81% or 82%.
Did you try on the sheet I supplied? Changing FALSE to TRUE for the HLOOKUP and VLOOKUP should do what you ask.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,994
Messages
5,599,250
Members
414,299
Latest member
thenewworld

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top