asked on

Excel Lookup Formula

Hello Experts,

In cell c1, I am creating a formula that will reference a % in cell a1 that is pulled from the table below which is off to the side. I want the formula to look at cell a1 to see the % but pull the # from the next higher level the %. For example: A1 is 50% so in the formula I want to use 75. Is there are way to do this?

5.0%      50
6.5%      75
7.0%      90
7.5%      110
8.0%      130

Thank you!

Patrick Matthews

If A1 = 50%, please explain how you would expect the result to be 75.

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

guswebb

I believe he meant A1 = 5.0% so the result should be 75 (taken from B2).

I'm not clear on what values you are expecting to be in C1 in the first place. Will this column contain a range of values that you want to find a corresponding match for in column A to then pull out a value from column B (adjacent and then one row down)? Will you then want the result of that lookup to appear in column D?

byundt

Both the VLOOKUP and INDEX & MATCH formulas are expecting the first column of the lookup table to contain the lowest possible value in each bracket. So you may want to change the percentages to 5.01%, 6.51%, etc.

ASKER CERTIFIED SOLUTION

guswebb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FFNStaff

ASKER

Thank you both for your help! Your answers worked perfectly.

Pat