Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
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 "Ben" wrote in message ... | 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
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 "Ben" wrote in message ... | 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
Also, So if did say...
=VLOOKUP(A18, [Sheet2]T01!A8:Z731,26,FALSE) .... Would i be able to do that? Thanks again. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
=VLOOKUP(A18,[Book2.xls]T01!$A$8:$Z$731,26,FALSE)
would be doable. Gord Dibben MS Excel MVP On Mon, 8 Sep 2008 10:40:07 -0700, Ben wrote: Also, So if did say... =VLOOKUP(A18, [Sheet2]T01!A8:Z731,26,FALSE) ... Would i be able to do that? Thanks again. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
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 "Gord Dibben" wrote: =VLOOKUP(A18,[Book2.xls]T01!$A$8:$Z$731,26,FALSE) would be doable. Gord Dibben MS Excel MVP On Mon, 8 Sep 2008 10:40:07 -0700, Ben wrote: Also, So if did say... =VLOOKUP(A18, [Sheet2]T01!A8:Z731,26,FALSE) ... Would i be able to do that? Thanks again. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
Hi Ben,
Read this tutorial by Excel MVP Debra Dalgleish: http://www.contextures.com/xlFunctions02.html -- Kind regards, Niek Otten Microsoft MVP - Excel "Ben" wrote in message ... | 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 | | | "Ben" wrote in message ... | | 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! | | | |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
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" wrote: 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 "Gord Dibben" wrote: =VLOOKUP(A18,[Book2.xls]T01!$A$8:$Z$731,26,FALSE) would be doable. Gord Dibben MS Excel MVP On Mon, 8 Sep 2008 10:40:07 -0700, Ben wrote: Also, So if did say... =VLOOKUP(A18, [Sheet2]T01!A8:Z731,26,FALSE) ... Would i be able to do that? Thanks again. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
Thank you for your input BJ. The necessary components (function arguments
dialog box) were not helpful at all as i wasnt 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 inns 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 wouldnt mind helping those who want to learn more about their software. Hence all the questions. I do apologise again. Regards & Gratitude, Ben "BJ" wrote: 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 ... |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
So Ben have all your questions been answered or do you still need some help?
"Ben" wrote: Thank you for your input BJ. The necessary components (function arguments dialog box) were not helpful at all as i wasnt 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 inns 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 wouldnt mind helping those who want to learn more about their software. Hence all the questions. I do apologise again. Regards & Gratitude, Ben "BJ" wrote: 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 ... |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
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/ex...091181033.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" wrote: So Ben have all your questions been answered or do you still need some help? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Help
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 "Ben" wrote: 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/ex...091181033.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" wrote: So Ben have all your questions been answered or do you still need some help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |