Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP #N/A Error
Hi,
I'm using a VLOOKUP formula and I'm getting a #N/A error! The calculation steps show error at the 'lookup_value'.I've tried by best but cannot work through it.Can you please let me know how to get rid of this. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP #N/A Error
Look in the help index for ISNA -- Don Guillett Microsoft MVP Excel SalesAid Software "NM" wrote in message ... Hi, I'm using a VLOOKUP formula and I'm getting a #N/A error! The calculation steps show error at the 'lookup_value'.I've tried by best but cannot work through it.Can you please let me know how to get rid of this. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP #N/A Error
Suppose you are using
=VLOOKUP(A1,C:D,2,False) Then make sure that A1 is one of the values in Col C... If your fourth parameter is TRUE or you are not using one then make sure that Col C & D are sorted on Col C... If you are using a formula in A1, then make sure it returns a valid value... Just to test replace A1 by 10 and add one row with 10 in Col C and TEST in Col D you should get TEST as a result of the formula above. "NM" wrote: Hi, I'm using a VLOOKUP formula and I'm getting a #N/A error! The calculation steps show error at the 'lookup_value'.I've tried by best but cannot work through it.Can you please let me know how to get rid of this. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP #N/A Error
would you post your formula and elaborate more what you want to do.
if your formula is correct, using ISNA with the Vlookup formula should solve your problem "NM" wrote: Hi, I'm using a VLOOKUP formula and I'm getting a #N/A error! The calculation steps show error at the 'lookup_value'.I've tried by best but cannot work through it.Can you please let me know how to get rid of this. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP #N/A Error
Thanks Sheeloo! With your help I was able to fix my #N/A error.
Another question: What is the fastest way to calculate the column index number.Say for eg. how do you calculate the column index number for columns which fall to the extreme right? I mean my column index is BR, say my table range starts from A, what will be the coulmn index number for BR? Do I count all the way to BR? Thanks for your help. "Sheeloo" wrote: Suppose you are using =VLOOKUP(A1,C:D,2,False) Then make sure that A1 is one of the values in Col C... If your fourth parameter is TRUE or you are not using one then make sure that Col C & D are sorted on Col C... If you are using a formula in A1, then make sure it returns a valid value... Just to test replace A1 by 10 and add one row with 10 in Col C and TEST in Col D you should get TEST as a result of the formula above. "NM" wrote: Hi, I'm using a VLOOKUP formula and I'm getting a #N/A error! The calculation steps show error at the 'lookup_value'.I've tried by best but cannot work through it.Can you please let me know how to get rid of this. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP #N/A Error
If you have unique column headers then you can use a MATCH function to get
the column_index_number for you. ...........A..........B..........C..........D 1....Name......H1........H2........H3 2....Sue..........x...........y...........z 3....Joe..........a...........b...........c Where Hn = column headers To lookup Joe and H2: =VLOOKUP("Joe",A1:D3,MATCH("H2",A1:D1,0),0) Result = b -- Biff Microsoft Excel MVP "NM" wrote in message ... Thanks Sheeloo! With your help I was able to fix my #N/A error. Another question: What is the fastest way to calculate the column index number.Say for eg. how do you calculate the column index number for columns which fall to the extreme right? I mean my column index is BR, say my table range starts from A, what will be the coulmn index number for BR? Do I count all the way to BR? Thanks for your help. "Sheeloo" wrote: Suppose you are using =VLOOKUP(A1,C:D,2,False) Then make sure that A1 is one of the values in Col C... If your fourth parameter is TRUE or you are not using one then make sure that Col C & D are sorted on Col C... If you are using a formula in A1, then make sure it returns a valid value... Just to test replace A1 by 10 and add one row with 10 in Col C and TEST in Col D you should get TEST as a result of the formula above. "NM" wrote: Hi, I'm using a VLOOKUP formula and I'm getting a #N/A error! The calculation steps show error at the 'lookup_value'.I've tried by best but cannot work through it.Can you please let me know how to get rid of this. Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP #N/A Error
I think my question was not clear enough. In VLOOKUP, when we enter column index number, how do you quickly count the number for column BR. eg. the table range is a1: BZ100. Then in VLOOKUP function what number do you put for column BR in the column index number. Do I count all thy from A through BR? A=1 ,B=2, C=3 ....BR=? Let m eknow if I'm not clear. Thanks! "T. Valko" wrote: If you have unique column headers then you can use a MATCH function to get the column_index_number for you. ...........A..........B..........C..........D 1....Name......H1........H2........H3 2....Sue..........x...........y...........z 3....Joe..........a...........b...........c Where Hn = column headers To lookup Joe and H2: =VLOOKUP("Joe",A1:D3,MATCH("H2",A1:D1,0),0) Result = b -- Biff Microsoft Excel MVP "NM" wrote in message ... Thanks Sheeloo! With your help I was able to fix my #N/A error. Another question: What is the fastest way to calculate the column index number.Say for eg. how do you calculate the column index number for columns which fall to the extreme right? I mean my column index is BR, say my table range starts from A, what will be the coulmn index number for BR? Do I count all the way to BR? Thanks for your help. "Sheeloo" wrote: Suppose you are using =VLOOKUP(A1,C:D,2,False) Then make sure that A1 is one of the values in Col C... If your fourth parameter is TRUE or you are not using one then make sure that Col C & D are sorted on Col C... If you are using a formula in A1, then make sure it returns a valid value... Just to test replace A1 by 10 and add one row with 10 in Col C and TEST in Col D you should get TEST as a result of the formula above. "NM" wrote: Hi, I'm using a VLOOKUP formula and I'm getting a #N/A error! The calculation steps show error at the 'lookup_value'.I've tried by best but cannot work through it.Can you please let me know how to get rid of this. Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP #N/A Error
I think my question was not clear enough.
No, it was clear! You're just not following what I tried to explain. The column_index_number is *relative* to the lookup_table. If your table range is A1:BZ100 then the column_index_number for column BR would be the same as the column number for column BR. Column BR = 70 and this would also be the column_index_number *realtive* to the lookup_table. So, you could use this: =VLOOKUP("Joe",A1:BZ100,COLUMN(BR1),0) Which evaluates to: =VLOOKUP("Joe",A1:BZ100,70,0) However, if the table range was AA1:BZ100 then the column_index_number is no longer the same as the column number for column BR (70). The column_index_number for column BR would now be 44. As I tried to explain in my other post, when this is the case then you can use the MATCH function to *automatically* calculate the column_index_number for you instead of trying to figure it out in your head or using some sort of offset adjustment. -- Biff Microsoft Excel MVP "NM" wrote in message ... I think my question was not clear enough. In VLOOKUP, when we enter column index number, how do you quickly count the number for column BR. eg. the table range is a1: BZ100. Then in VLOOKUP function what number do you put for column BR in the column index number. Do I count all thy from A through BR? A=1 ,B=2, C=3 ....BR=? Let m eknow if I'm not clear. Thanks! "T. Valko" wrote: If you have unique column headers then you can use a MATCH function to get the column_index_number for you. ...........A..........B..........C..........D 1....Name......H1........H2........H3 2....Sue..........x...........y...........z 3....Joe..........a...........b...........c Where Hn = column headers To lookup Joe and H2: =VLOOKUP("Joe",A1:D3,MATCH("H2",A1:D1,0),0) Result = b -- Biff Microsoft Excel MVP "NM" wrote in message ... Thanks Sheeloo! With your help I was able to fix my #N/A error. Another question: What is the fastest way to calculate the column index number.Say for eg. how do you calculate the column index number for columns which fall to the extreme right? I mean my column index is BR, say my table range starts from A, what will be the coulmn index number for BR? Do I count all the way to BR? Thanks for your help. "Sheeloo" wrote: Suppose you are using =VLOOKUP(A1,C:D,2,False) Then make sure that A1 is one of the values in Col C... If your fourth parameter is TRUE or you are not using one then make sure that Col C & D are sorted on Col C... If you are using a formula in A1, then make sure it returns a valid value... Just to test replace A1 by 10 and add one row with 10 in Col C and TEST in Col D you should get TEST as a result of the formula above. "NM" wrote: Hi, I'm using a VLOOKUP formula and I'm getting a #N/A error! The calculation steps show error at the 'lookup_value'.I've tried by best but cannot work through it.Can you please let me know how to get rid of this. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP ERROR | Excel Worksheet Functions | |||
Vlookup value not available error | Excel Discussion (Misc queries) | |||
Vlookup Error, please help | Excel Discussion (Misc queries) | |||
vlookup error!! | Excel Worksheet Functions | |||
vlookup error | Excel Worksheet Functions |