# 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
###### Who is Participating?

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.

Commented:
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
Commented:
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
Commented:
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
Commented:
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
Author Commented:

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
Commented:
Pat,

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

Saurabh...
0
Author 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
Commented:
For the value of 72% ,,what answer you are looking for??
0
Commented:
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