Excel 2007 : Issue with vlookup and approximate match

Hello Excel Masters

I have Table like this (sorted in ascending order):


Column1 Column2
100% 115%
101% 116%
102% 117%
104% 118%
106% 119%
107% 120%

and I am trying to use vlookup function with approximate match for 103,2%. Logically the vlookup function should return 118% as 103% this is closer to 104% than to 102%, but I receive 117%. That's understandable after I read that:

"Excel works its way down the first column until it finds a value greater than the lookup value. Then it backs up one row to return its results."

However I would like vlookup to give me 118% for 103,2% and in other case - 117% for 102,7%, but in both cases I receive 117%.

Does anyone can help me to solve this issue? I would be really gratefull!

Ps. Column 1 contains only integer percentages

Thanks in advance,
Sylwia

Assuming data is in A2:B7, and your values of 103,2% is in E2, then try:

=INDEX($B$2:$B$7;MATCH(MIN(ABS($A$2:$A$7-E2));ABS($A$2:$A$7-E2);0))

adjust ranges and/or function names to suit.. and confirm with CTRL+SHIFT+ENTER not just ENTER and copy down.

FYI, in Polish:

=INDEKS($B$2:$B$7;PODAJ.POZYCJĘ(MIN(MODUŁ.LICZBY($A$2:$A$7-E2));MODUŁ.LICZBY($A$2:$A$7-E2);0))

Thanks for this solution, I was trying to implement it into my real formula which is more enhanced and I think I need to present the problem once again.

In fact I have few columns and they appear like this:

ColumnA ColumnB ColumnC ColumnD ColumnE
Country1 Country1 Country2 Country2
105% 130% 100% 115%
106% 131% 101% 116%
107% 132% 102% 117%
109% 133% 104% 118%
111% 134% 106% 119%
112% 135% 107% 120%

ColumnA is empty, data start from column B.

Let's assume the value which I try to match (103,2%) belongs to Country2 and is in F2. Implementing your solution into my formula it now looks like this:

=INDEX(OFFSET(A:A;;MATCH("Country2";$B$1:$E$1;0)+1;;1);MATCH(MIN(ABS(OFFSET(A:A;;MATCH("Country2";$B$1:$E$1;0);;1)-F2));ABS(OFFSET(A:A;;MATCH("Country2";$B$1:$E$1;0);;1)-F2);0))

Then I press CTRL+SHIFT+ENTER and I receive lovely #N/A.

Any help would be appreciated! Thanks!

PS. Your formula in Polish rocks!

Try:

Please Login or Register  to view this content.

with CTRL+SHIFT+ENTER confirmation