Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup not working properly
Hi I'm currently running office 2000 and trying to get vlookup to work
properly. I have 10 tables which must be referenced from one formula. The formula must pick which table to use (currently have that working) the formula must pick which row and column to use (currently have that working) I know that that I just said the formula works and it does. However it only works for numbers = 76 and I need it to work for any numbers entered Here is the exact formula being used: =IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1 :U151"),Data!E3,TRUE))=TRUE," ",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Da ta!E3,FALSE)) As I say this formula works just fine for me for numbers 76-150 (150 is the highest number on the table) and returns the correct result. Numbers 1-75 however do not return anything and there is data in the table that should be returned. (Note there are 151 rows in the tables on the other sheets) The tables contain both numeric and alphanumeric data and where the formula returns data both are returned fine. One thing the formula does not do that I would like it to (though its not neccessary) is in the event of no exact match returning the next largest number that is smaller than the search value. Hopefully this is a fixable problem. If you need additonal info please just let me know. Jaime |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup not working properly
Nel post
*Jaime* ha scritto: Hi I'm currently running office 2000 and trying to get vlookup to work properly. I have 10 tables which must be referenced from one formula. The formula must pick which table to use (currently have that working) the formula must pick which row and column to use (currently have that working) I know that that I just said the formula works and it does. However it only works for numbers = 76 and I need it to work for any numbers entered Here is the exact formula being used: =IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1 :U151"),Data!E3,TRUE))=TRUE," ",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Da ta!E3,FALSE)) As I say this formula works just fine for me for numbers 76-150 (150 is the highest number on the table) and returns the correct result. Numbers 1-75 however do not return anything and there is data in the table that should be returned. (Note there are 151 rows in the tables on the other sheets) The tables contain both numeric and alphanumeric data and where the formula returns data both are returned fine. One thing the formula does not do that I would like it to (though its not neccessary) is in the event of no exact match returning the next largest number that is smaller than the search value. Hopefully this is a fixable problem. If you need additonal info please just let me know. Jaime Hi Jaime, Without your file in front of me, the only thing I can suggest to you is to put TRUE or FALSE in the VLOOKUPs in the same manner: or 2 TRUE or 2 FALSE... -- (I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup not working properly
Hi!
You have conflicting range_lookup arguments: Data!E3,TRUE))...............Data!E3,FALSE)) Try the formula like this: =IF(ISNA(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1:U151" ),Data!E3)),"",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1 :U151"),Data!E3)) This requires the table_array be sorted ascending. Biff "Jaime" wrote in message ... Hi I'm currently running office 2000 and trying to get vlookup to work properly. I have 10 tables which must be referenced from one formula. The formula must pick which table to use (currently have that working) the formula must pick which row and column to use (currently have that working) I know that that I just said the formula works and it does. However it only works for numbers = 76 and I need it to work for any numbers entered Here is the exact formula being used: =IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1 :U151"),Data!E3,TRUE))=TRUE," ",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Da ta!E3,FALSE)) As I say this formula works just fine for me for numbers 76-150 (150 is the highest number on the table) and returns the correct result. Numbers 1-75 however do not return anything and there is data in the table that should be returned. (Note there are 151 rows in the tables on the other sheets) The tables contain both numeric and alphanumeric data and where the formula returns data both are returned fine. One thing the formula does not do that I would like it to (though its not neccessary) is in the event of no exact match returning the next largest number that is smaller than the search value. Hopefully this is a fixable problem. If you need additonal info please just let me know. Jaime |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup not working properly
Thanks for the help. Unfortuantly my tables are in decending order though.
However with your help I found the problem. Formula is now =IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1:U1 51"),Data!E3,FALSE))=TRUE," ",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1:U151"),Data! E3,FALSE)) (Putting both cases of FALSE to TRUE causes the formula to not return anything.) Interestlying enough with the formula I had before I got it to work when I specified a range of A1:U301 don't know why this is. Unfortunatly the formula I have now doesn't get the largest number that is smaller than my search result. but that can wait untill I figure out what is wrong or get all 10 tables switched to assending format. Thanks for the great and quick help. Jaime "Biff" wrote: Hi! You have conflicting range_lookup arguments: Data!E3,TRUE))...............Data!E3,FALSE)) Try the formula like this: =IF(ISNA(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1:U151" ),Data!E3)),"",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1 :U151"),Data!E3)) This requires the table_array be sorted ascending. Biff "Jaime" wrote in message ... Hi I'm currently running office 2000 and trying to get vlookup to work properly. I have 10 tables which must be referenced from one formula. The formula must pick which table to use (currently have that working) the formula must pick which row and column to use (currently have that working) I know that that I just said the formula works and it does. However it only works for numbers = 76 and I need it to work for any numbers entered Here is the exact formula being used: =IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1 :U151"),Data!E3,TRUE))=TRUE," ",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Da ta!E3,FALSE)) As I say this formula works just fine for me for numbers 76-150 (150 is the highest number on the table) and returns the correct result. Numbers 1-75 however do not return anything and there is data in the table that should be returned. (Note there are 151 rows in the tables on the other sheets) The tables contain both numeric and alphanumeric data and where the formula returns data both are returned fine. One thing the formula does not do that I would like it to (though its not neccessary) is in the event of no exact match returning the next largest number that is smaller than the search value. Hopefully this is a fixable problem. If you need additonal info please just let me know. Jaime |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP stops working with pasted values | Excel Worksheet Functions | |||
How do I allow for multiple values in VLOOKUP? | Excel Worksheet Functions | |||
vlookup issue ( not working and im tearing my hair out) | Excel Discussion (Misc queries) | |||
VLOOKUP Problem (limitation)? | Excel Worksheet Functions | |||
MODE isn't working properly. | Excel Worksheet Functions |