My vlookup is not working

u587162
u587162 used Ask the Experts™
on
I've run many vlookups perfectly fine without problems however I have come across one that I cannot do.  I cant post the 2 spreadsheets because of the sensitive data on there but essentially when i write the formula in a particular cell where I was the vlookup to be conducted, the "=vlookup blah blah" appears in the cell in question.

What could I possibly be doing wrong?  The spreadsheet I am trying to lookup has the lookup client name in the far left column and there is no left or blank column on the spreadsheet.

Also please confirm when or where or what the rules that apply when doing a vlookup in the following situations:

- where the spreadsheet that is to be looked-up does not have the identifier in the far left (first column)

- where the identifier column in the looked-up spreadsheet is not in alphabetical order

- where in the workings speadsheets (that contains the formula) the identifier column is not the first column

- where the identifier column in the workings spreadsheet is not in alphabetical order

I need to understand the consequences of each of the above scenarios pls.

Thx.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Select the cell and format it as general
Top Expert 2008

Commented:
Is there a space or single quote before the equal sign?

Is the cell formatted as "Text"? If so format it as "General".

Kevin
You might also consider copying one of the working vlookups and then modify it to suit.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
ok I will need to check when Im back in the office tomorrow.  But could you please also ans my points about the consequences if the first col is not ordered alphabetically etc in the source or lookup spreadsheet etc or if not the first col...

There is no space or single quote in front of the formula.  I'll need to check the formatting.  Its possible it might be formatted, but not sure why that would affect it?
Top Expert 2008

Commented:
If the fourth parameter is True then you are using the range look up option and your table must be in ascending sorted order.

But if you are using False then the table can be in any order.

Kevin

Author

Commented:
Kevin,
Could you please elaborate on your last ans, I didnt understand, thanks.
Top Expert 2008
Commented:
VLOOKUP has four parameters. The fourth or last parameter can be True or False. It's optional and if omitted then it's assumed to be False.

Passing False or omitting the parameter tells VLOOKUP to use exact matching. This means that the lookup value must be in the table or an error (#N/A) will be returned. In this mode the table does not have to be sorted.

But suppose we want to find a value in a range such as determining the fine for an overdue library book. For this we can use the range match version of VLOOKUP. In the figure below, the yellow lookup table contains fine amounts. If a book is less than three days late, there is no fine. If it is three or more days late but less than 10 days late, there is a $15 fine. In this system, what would the penalty be for something 7 days late? A human can tell this is a $15 fine, but VLOOKUP will not find 7 in the first column of the lookup table. When you tell Excel to use the range version of VLOOKUP, it will look for 7 but if it can’t find 7, it will use the row with the closest value that is smaller than 7. For this mode to work the table must be sorted in ascending order.

.
Kevin

Author

Commented:
Hi Kevin,

Thanks for the detailed explanation.  You mentioned the #N/A.  How can the Vlookup statement be written such that instead of #N/A being displayed, either it is left blank or some other word written instead e.g. "Not found"?

Many thanks.
Top Expert 2008

Commented:
If you are working in Excel 2007 or later then use the IFERROR function:

=IFERROR(VLOOKUP(...),"Value if Error")

If 2003 or earlier then:

=If(ISERROR(VLOOKUP(...),"Value if Error",VLOOKUP(...))

Kevin

Author

Commented:
Thx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial