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?
 
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
 
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.