Vlookup Help

[Ben]

Hello all,

I am currently vlookup'ing information off another document and have run
into problems.

My vlookup statment...

=VLOOKUP(A18, [Sheet2]T01!A8:A731,1,FALSE)

Now, that statment works Brilliantly. However, when i change the colum range
to '2', e.g...

=VLOOKUP(A18, [Sheet2]T01!A8:A731,2,FALSE)

.... i get all sorts of problems... mainly, "#REF!"

I do not have a clue as to why this is the case as i just want the info from
that particualr colum.

Help and Thanks!

 

[Niek Otten]

Hi Ben,

=VLOOKUP(A18, [Sheet2]T01!A8:B731,2,FALSE)

There isn't a second column in the range A8:A731....
--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hello all,
|
| I am currently vlookup'ing information off another document and have run
| into problems.
|
| My vlookup statment...
|
| =VLOOKUP(A18, [Sheet2]T01!A8:A731,1,FALSE)
|
| Now, that statment works Brilliantly. However, when i change the colum range
| to '2', e.g...
|
| =VLOOKUP(A18, [Sheet2]T01!A8:A731,2,FALSE)
|
| ... i get all sorts of problems... mainly, "#REF!"
|
| I do not have a clue as to why this is the case as i just want the info from
| that particualr colum.
|
| Help and Thanks!

 

[Ben]

Hello Niek,

I must admit, i am a little bit of a novice but do have a good understanding
with the functions and what not (currently at university studying computer
science).

I thought that the statement "A8:B731" was the fields linking the two excel
documents (Matching figures within the two). Thus, when the vlookup finds
those figures, it will take the 2nd columns field in that row?
- So, does that mean that i can have the range A8:Z800 or any set of
parameters or criterias? Yet, what if i don't want to search all of the
fields between A & Z ? (just for me, not the document as there all unique
numbers) Do you have to do something like A8:A731;Z8:Z731 ?

I thought that the A8:A731 links the two excel documents.

I also read that vlookup only works in the first column of cells (A:A) but
is this wrong?

Also, on another note and confirmation, is the "(A18," bit meaning look up
that cell? - The Microsoft help site thingy is only moderately helpful hence
my attempt for the first time.

Sorry to bombard you with questions but i am quite intrigued. Thanks for the
quick reply and help.

Regards,


Ben

 

[Ben]

Also, So if did say...

=VLOOKUP(A18, [Sheet2]T01!A8:Z731,26,FALSE)

.... Would i be able to do that?

Thanks again.

 

[Gord Dibben]

=VLOOKUP(A18,[Book2.xls]T01!$A$8:$Z$731,26,FALSE)

would be doable.


Gord Dibben MS Excel MVP

 

[Ben]

It would be nice if you would answer my other questions as "would be doable"
isn't really giving me any justification or insight for using the particular
vlookup stated.

I don't just want a simple answer as i have a fondness to learn, expanding
my knowledge of the use of the software. I would appriciate it if a Microsoft
representative (mainly Niek as he seems helpful) would clearly answer my
questionsas i thought the community forum was ment to show people why and
justify why the above statment is "doable". - Why, can i do it a simpler and
more conveiniant way?


Regards & Gratitude (if i get a valid explanation).

Ben

=VLOOKUP(A18,[Book2.xls]T01!$A$8:$Z$731,26,FALSE)

would be doable.


Gord Dibben MS Excel MVP

Also, So if did say...

=VLOOKUP(A18, [Sheet2]T01!A8:Z731,26,FALSE)

... Would i be able to do that?

Thanks again.

 

[Niek Otten]

Hi Ben,

Read this tutorial by Excel MVP Debra Dalgleish:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello Niek,
|
| I must admit, i am a little bit of a novice but do have a good understanding
| with the functions and what not (currently at university studying computer
| science).
|
| I thought that the statement "A8:B731" was the fields linking the two excel
| documents (Matching figures within the two). Thus, when the vlookup finds
| those figures, it will take the 2nd columns field in that row?
| - So, does that mean that i can have the range A8:Z800 or any set of
| parameters or criterias? Yet, what if i don't want to search all of the
| fields between A & Z ? (just for me, not the document as there all unique
| numbers) Do you have to do something like A8:A731;Z8:Z731 ?
|
| I thought that the A8:A731 links the two excel documents.
|
| I also read that vlookup only works in the first column of cells (A:A) but
| is this wrong?
|
| Also, on another note and confirmation, is the "(A18," bit meaning look up
| that cell? - The Microsoft help site thingy is only moderately helpful hence
| my attempt for the first time.
|
| Sorry to bombard you with questions but i am quite intrigued. Thanks for the
| quick reply and help.
|
| Regards,
|
|
| Ben
|
|
|
| "Niek Otten" wrote:
|
| > Hi Ben,
| >
| > =VLOOKUP(A18, [Sheet2]T01!A8:B731,2,FALSE)
| >
| > There isn't a second column in the range A8:A731....
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | Hello all,
| > |
| > | I am currently vlookup'ing information off another document and have run
| > | into problems.
| > |
| > | My vlookup statment...
| > |
| > | =VLOOKUP(A18, [Sheet2]T01!A8:A731,1,FALSE)
| > |
| > | Now, that statment works Brilliantly. However, when i change the colum range
| > | to '2', e.g...
| > |
| > | =VLOOKUP(A18, [Sheet2]T01!A8:A731,2,FALSE)
| > |
| > | ... i get all sorts of problems... mainly, "#REF!"
| > |
| > | I do not have a clue as to why this is the case as i just want the info from
| > | that particualr colum.
| > |
| > | Help and Thanks!
| >
| >
| >

 

[BJ]

I would suggest going to your formula in your worksheet and while you have
the cell highlighted click on the 'Insert Function' button [the 'fx' symbol
just to the left of the cell entry area]. This will show you in a separate
dialog box the Function Arguments of your formula. You can then select
components of the formula by clicking in the cell entry and the matching
function along with its result "=" will be highlighted in the dialog box.

By doing this you can see if you have all of the necessary components of a
function and if they are working as you had hoped they would.

The formula results you get can generally be deciphered from the MS Help
function. For instance, if you receive a #REF! result, the column index
number is greater than the number of columns in the array - as was the case
in your first posted example when you changed the "1" to a "2".

The folks on this board are extremely helpful but they generally answer what
your question requests - as you can see there are hundreds of questions asked
daily and they can only do so much. Your post asked if you could "do" a
certain function and you received the reply that "yes, that is doable." If
you wanted a more in-depth explanation then ask for it ... the folks here,
whether MVPs (who are not representatives of MS) are always willing to
dispense their vast stores of knowledge on the rest of us dolts ...

You'll get a lot further with sugar than you will with vinegar ...

It would be nice if you would answer my other questions as "would be doable"
isn't really giving me any justification or insight for using the particular
vlookup stated.

I don't just want a simple answer as i have a fondness to learn, expanding
my knowledge of the use of the software. I would appriciate it if a Microsoft
representative (mainly Niek as he seems helpful) would clearly answer my
questionsas i thought the community forum was ment to show people why and
justify why the above statment is "doable". - Why, can i do it a simpler and
more conveiniant way?


Regards & Gratitude (if i get a valid explanation).

Ben

=VLOOKUP(A18,[Book2.xls]T01!$A$8:$Z$731,26,FALSE)

would be doable.


Gord Dibben MS Excel MVP

Also, So if did say...

=VLOOKUP(A18, [Sheet2]T01!A8:Z731,26,FALSE)

... Would i be able to do that?

Thanks again.

 

[Ben]

Thank you for your input BJ. – The necessary components (function arguments
dialog box) were not helpful at all as i wasn’t able to select my other excel
document.

I do apologise if i came across a bit crude and canny, however, I like to
understand in the inn’s and out's of what i am actually learning. - I am not
just looking for an easy way out. I know it is incredible helpful having
these rewarded chaps here and i am grateful and understand that everyone else
is also in need of help.

However, would it honestly take more than 5 minutes for an excel ‘expert’ to
share their own independent knowledge and not give a textbook answer? – The
syntax site regarding Vlookups helped obviously as my first attempt (above)
did come close to a “doable†attempt but some other valid explanation would
have been nice.

I do appreciate the help received. Honestly i do, but i just want to
understand what it is i am specifically using within the spreadsheet. I also
thought that Microsoft’ wouldn’t mind helping those who want to learn more
about their software. Hence all the questions.

I do apologise again.

Regards & Gratitude,

Ben

 

[BJ]

So Ben have all your questions been answered or do you still need some help?

Thank you for your input BJ. – The necessary components (function arguments
dialog box) were not helpful at all as i wasn’t able to select my other excel
document.

I do apologise if i came across a bit crude and canny, however, I like to
understand in the inn’s and out's of what i am actually learning. - I am not
just looking for an easy way out. I know it is incredible helpful having
these rewarded chaps here and i am grateful and understand that everyone else
is also in need of help.

However, would it honestly take more than 5 minutes for an excel ‘expert’ to
share their own independent knowledge and not give a textbook answer? – The
syntax site regarding Vlookups helped obviously as my first attempt (above)
did come close to a “doable†attempt but some other valid explanation would
have been nice.

I do appreciate the help received. Honestly i do, but i just want to
understand what it is i am specifically using within the spreadsheet. I also
thought that Microsoft’ wouldn’t mind helping those who want to learn more
about their software. Hence all the questions.

I do apologise again.

Regards & Gratitude,

Ben

I would suggest going to your formula in your worksheet and while you have
the cell highlighted click on the 'Insert Function' button [the 'fx' symbol
just to the left of the cell entry area]. This will show you in a separate
dialog box the Function Arguments of your formula. You can then select
components of the formula by clicking in the cell entry and the matching
function along with its result "=" will be highlighted in the dialog box.

By doing this you can see if you have all of the necessary components of a
function and if they are working as you had hoped they would.

The formula results you get can generally be deciphered from the MS Help
function. For instance, if you receive a #REF! result, the column index
number is greater than the number of columns in the array - as was the case
in your first posted example when you changed the "1" to a "2".

The folks on this board are extremely helpful but they generally answer what
your question requests - as you can see there are hundreds of questions asked
daily and they can only do so much. Your post asked if you could "do" a
certain function and you received the reply that "yes, that is doable." If
you wanted a more in-depth explanation then ask for it ... the folks here,
whether MVPs (who are not representatives of MS) are always willing to
dispense their vast stores of knowledge on the rest of us dolts ...

You'll get a lot further with sugar than you will with vinegar ...

 

[Ben]

Put simply, Yes.

However, i was just wondering if there was some way to change the output of
vlookup when it is unable to find any data.

For example, i just want to change the "0" when no data is found to say
"Missing".

I checked on the MS office site regarding If statments and looked at other
posts regarding dynamic statments and found that i am having problems.

I would assume that this statment would work as the MS site explains...
http://office.microsoft.com/en-us/excel/HP052091181033.aspx

I beleive i correctly perform an IF statment...

If(A10="0","Missing","")

However, when i throw on the end (and it is the linking bit)...

=If(A10="0","Missing","")*VLOOKUP(A14,[Book2.xls]T01!$A$8:$AB$731,28,FALSE)

So, what am i doing wrong and why please?

Regards,

Ben

 

[BJ]

Hi Ben

If you are attempting to locate a lookup that is not in your array's lookup
column you will get an answer of #N/A.

There is a function within Excel - ISNA(value) - that can address this issue
and yes you could address it with an IF statement. The ISNA function is
"TRUE" if the value is #N/A or "ISNA."

So really your goal as I understand it is when the lookup is not in my array
- that is, whenever I get the #N/A answer - I want to show the term "MISSING"

Your IF statement (which is made up of a logical test and then a result if
that test is true and a result if that test is false - IF(logical test, value
if true, value if false) - would like this:

Your logical test would be ISNA(value) - where the value is your lookup
statement.
Your value if ISNA is true would be "MISSING"
Your value if ISNA is false would be your lookup statement

=IF(ISNA(VLOOKUP(A14,[Book2.xls]T01!$A$8:$AB$731,28,FALSE),"MISSING",VLOOKUP(A14,[Book2.xls]T01!$A$8:$AB$731,28,FALSE))

Try that, it should work.

BJ

Put simply, Yes.

However, i was just wondering if there was some way to change the output of
vlookup when it is unable to find any data.

For example, i just want to change the "0" when no data is found to say
"Missing".

I checked on the MS office site regarding If statments and looked at other
posts regarding dynamic statments and found that i am having problems.

I would assume that this statment would work as the MS site explains...
http://office.microsoft.com/en-us/excel/HP052091181033.aspx

I beleive i correctly perform an IF statment...

If(A10="0","Missing","")

However, when i throw on the end (and it is the linking bit)...

=If(A10="0","Missing","")*VLOOKUP(A14,[Book2.xls]T01!$A$8:$AB$731,28,FALSE)

So, what am i doing wrong and why please?

Regards,

Ben

So Ben have all your questions been answered or do you still need some help?