Excel 2010 Lookup

Hi,

I want to find a number on a grid then go to the next level.  Below is an example of the grid.  I am using Lookup to find the % and pull the $ then want to go to the next level higher.  For example, find the 50%  $'s and get $75,000 as the answer.  How can I do this?

50%           50,000
65%           75,000
70%           90,000

Thank you,

Pat
FFNStaffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Pat,

I believe this is what you are looking for..I have assumed your table is sorted as shown in your example..enclosed is the workbook for your reference...

Saurabh...
Values.xlsx
0
akbCommented:
The solution is to use =+INDIRECT(ADDRESS(MATCH(D3,A1:A3,0)+1,2)) as per the attached example.

EDIT: A few minutes late but essentially the same solution as Saurabh's.
Sample1.xlsx
0
barry houdiniCommented:
Hello Pat,

Will the "lookup value" always exist in the table? What if the lookup value is 55%, is that possible - what result would you expect in that case?

Also, if the lookup value is 70% what result do you expect then, there is no "next level"?

regards, barry
0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

scsymeCommented:
Here is an option that does not rely on the ordering of the records, but requires a new column to be introduced to determine the rank of each % and uses this to identify the next highest value.
Rank.xlsx
0
FFNStaffAuthor Commented:
Sorry for the slow response on these answers.  I had to get to the points Barry asked about.

Barry, you ask very good questions.

This table was just a sample, there are actually 13 levels.  Yes, there are intermediate levels but going up a level is always to the table.  Once at the top of the table, there are no more levels.  I have blank cells at the end of the table.

Saurahb, akb, your responses worked great until the items Barry asked about--intermediate %s and end of table.  When at the end of the table, there is a REF error.  

Pat
0
Saurabh Singh TeotiaCommented:
Pat,

Can you post your sample file where it gives you an error so that i can look in the same.

Saurabh...
0
FFNStaffAuthor Commented:
Saurabh,  below is the full table-I can't get the file to upload.  An example of an intermediate rate is 72% .  92% is the maximum on the table.

Thank you!

Grid      
50%           0
50%          50
65%          75
70%          90
75%        110
80%        130
82%        170
85%        228
88%        425
90%        550
91%        600
92%        650
0
Saurabh Singh TeotiaCommented:
For the value of 72% ,,what answer you are looking for??
0
barry houdiniCommented:
Are the first two values meant to be 50%? I assume the first one should be 0%

With table in A1:B12 and lookup value in E3 try this formula

=IFERROR(INDEX(B$1:B$12,MATCH(E3,A$1:A$12)+1),B$12)

Does that give the required results? See attached

regards, barry
lookup-table.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
scsymeCommented:
The solution I offered should work with all your requirements as long as you are in a position to add the rank column.
0
FFNStaffAuthor Commented:
Thank all of you for your participation in the question.  I appreciate all of your help!!  Pat
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.