V-lookup to find approximate value not working | Excel forum

Resolved · Urgent Priority · Version 2010

Thu 29 Mar 2018, 15:53 Delegate Silvia said...

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.

Thu 29 Mar 2018, 21:24 Trainer Jens said...

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

Thu 5 Apr 2018: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Shortcut for deleting all comments in a spreadsheet

If you have entered multiple comments into a spreadsheet and wish to delete them all at once, you can achieve this by:

1. Holding down Ctrl, then Shift, then O - this will select all cells containing comments in the worksheet you are looking at.

2. Right-clicking on one of the selected cells, and selecting Delete Comment from the menu that appears.

3. Clicking anywhere else in the spreadsheet to deselect comments - all comments should have disappeared from the spreadsheet.

View all Excel hints and tips