asked on # My vlookup is not working

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.

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.

Microsoft Excel

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
You might also consider copying one of the working vlookups and then modify it to suit.

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?

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?

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

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

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

Kevin

Kevin,

Could you please elaborate on your last ans, I didnt understand, thanks.

Could you please elaborate on your last ans, I didnt understand, thanks.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
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.

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.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

=IFERROR(VLOOKUP(...),"Val

If 2003 or earlier then:

=If(ISERROR(VLOOKUP(...),"

Kevin

Thx

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

Kevin