Vlookup for a whole column using cell reference | Excel forum

Resolved · High Priority · Version 2010

Fri 28 Apr 2017, 16:03 Delegate Liz said...

Liz has attended:
SharePoint course

Vlookup for a whole column using cell reference

I am using V lookup.
The values involved are number stored as text, both in the working spreadsheet and in the lookup area. Some of the formulas work, others don't, they return #N/A.

Why would this be and how can I correct it please?

Wed 3 May 2017, 09:54 Trainer Marius said...

RE: Vlookup for a whole column using cell reference

Hi Liz,

One possible solution I can suggest is writing the formula as follows:

=VLOOKUP(VALUE(E2),A1:B100,2,FALSE)

The Value function will look at the numeric values of the numbers stored as text.

If this doesn't work, you could select all numbers stored as text and convert them to numbers (if you are allowed to do this).

Alternatively, check the numbers in the rows where you get error messages for spaces before or after the numbers. Spaces cause mismatches. You could use the Trim function to remove spaces.

I hope this helps.

Kind regards
Marius Barnard
Excel Trainer

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

The Easiest Way to See a Sum or Average in Excel 2010

Did you know the quickest and most simple way of working out the average or sum of a set of numbers?

Highlight numbers in cells or type some numbers in cells and then look at the status bar at the bottom of the window. Here you will see a display of the average of the numbers, as well as a count of the cells and the sum of these cells.

View all Excel hints and tips