Combination of an exact lookup and an inexact (TRUE) lookup...

[Eslava]

I have this data set:

Excel Workbook
	B	C	D
6	City	Salary	% Difference from National
7	Akron, OH	20,000	***
8	Akron, OH	30,000	-1.50%
9	Akron, OH	40,000	-1.40%
10	Akron, OH	50,000	-1.40%
11	Akron, OH	60,000	-1.30%
12	Akron, OH	70,000	-1.40%
13	Akron, OH	80,000	-1.30%
14	Akron, OH	90,000	-1.20%
15	Akron, OH	100,000	-1.20%
16	Akron, OH	110,000	***
17	Akron, OH	120,000	***
18	Albany, NY	20,000	1.40%
19	Albany, NY	30,000	-2.00%
20	Albany, NY	40,000	-3.70%
21	Albany, NY	50,000	-4.10%
22	Albany, NY	60,000	-4.30%
23	Albany, NY	70,000	-4.60%
24	Albany, NY	80,000	-4.50%
25	Albany, NY	90,000	-4.40%
26	Albany, NY	100,000	-4.30%
27	Albany, NY	110,000	***
28	Albany, NY	120,000	***
120K

Excel 2007

I need to find two things: 1) the city and 2) where the incumbent's salary falls in between

in order to find the difference between the national average.

Can this be done???

 

[Eslava]

For instance, someone who is making $85,000 in Akron, OH is -1.3% from the national average. Someone who is making $85,000 in Albany, NY is -4.5% from the national average.

Can a formula be applied so that it will automatically pick up where they are from and where their salary falls?

 

[Eslava]

Any luck so far?

I tried INDEX, VLOOKUP, and SUMPRODUCT so far and nothing is working...

 

[Eslava]

bump

 

[Eslava]

bump

 

[barry houdini]

Let's say you have a specific city in B2 and salary in C2, try this formula in D2

=LOOKUP(C2,OFFSET(C6,MATCH(B2,B6:B27,0)-1,0,COUNTIF(B6:B27,B2),2))

format D2 as percentage

 

[Eslava]

it works!!

Thanks!