Silvia has attended:
No courses
V-lookup to find approximate value not working
Hi,
I wonder if someone can help me.
On Column A: rank by population size, Column B: list of UK cities, column C: is population, column D: is GDP.
On another table in Column K: I have a list of cities from other countries with their GDP.
Now, what I want is from Vlookup is to do return an UK city with an approximate GDP size based on the other country's GDP.
I used the normal VlookUp with =VLOOKUP(K2,A2:D36,2,TRUE)
And for some reason, it returns the city with the lowest value - like lowest value by far. I am talking returnin Paris GDP of 555 bn with Porthsmouth 6bn.
PLEASE HELP! AND YES, I have tried INDEX MATCH 1 and it does not return a consistent value because 1 will give you lower value and -1 will give higher values and what i want is approximate values.
RE: V-lookup to find approximate value not working
Hi Silvia,
Thank you for the forum question.
As you have already found out the Vlookup does not return approximate value but always the nearest lowest value.
You can nest a Round, Roundup, or a RoundDown function inside the Vlookup to get closer to what you want.
=VLOOKUP(Roundup(K2,-9),A2:D36,2,TRUE)
I do not know if Roundup(K2,-9) is the best solution for you, but test it.
There is complicated options, if you have array formula experience:
https://trumpexcel.com/find-closest-match-in-excel/
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
