Avatar of u587162
u587162
 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.
Microsoft Excel

Avatar of undefined
Last Comment
u587162

8/22/2022 - Mon
SOLUTION
Saqib Husain

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
Sign up - Free for 7 days
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
zorvek (Kevin Jones)

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

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

Kevin
Saqib Husain

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

ASKER
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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
zorvek (Kevin Jones)

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
u587162

ASKER
Kevin,
Could you please elaborate on your last ans, I didnt understand, thanks.
ASKER CERTIFIED SOLUTION
zorvek (Kevin Jones)

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
Sign up - Free for 7 days
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
u587162

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
zorvek (Kevin Jones)

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
u587162

ASKER
Thx