u587162

asked 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.

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.

Last Comment

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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

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?

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?

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

ASKER

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.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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.

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.

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

ASKER

Thx

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY

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

Kevin