|
excel lookup functions
how can i search a very long list of values to see if each individual value
in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an
error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
i get a formula error when i tried this...but that could be because i don't
fully understand the vlookup formula. is the "Range_lookup" field boolean? Do I even need a value in that field? can i not just do an IF statement that checks whether a value is in a range? like, IF B2:B65000 CONTAINS A1, THEN write "Yes", ELSE write "No" -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
Excel wants to put an asterisk in the formula...see below
=IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1) that makes the formula work, but it returns true/false instead of 0/1, i also checked enough of the records by hand to know a few of them are there, ie the formula should return "true", and the formula failed--all cells returned "false" But... lets say that i actually do use the vlookup function to look up a value in another column. the column i'm trying to search in contains a list of five digit numbers. if the number that i am searching for IS in that column, i want the Col_index_num value (which is a date) to be returned. well, that works fine for the numbers that ARE there, but the numbers that are NOT there, the formula still returns a date, only the date that it returns is in the row directly above where the number SHOULD BE. Therefore, my output is a bunch of dates, which is what i want, only i don't know which dates are correct (actually belong to a number that was found) and which dates are invalid. does this make sense? its pretty difficult to explain... -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
Not sure why you would use an asterisk.
Use: =IF(ISERROR(VLOOKUP(vlookupcriteria)),0,1) To your second point: it sounds like you are looking for an EXACT match, not an approximate match, and if that exact match is found you want it returned. Therefore, your VLOOKUP function should look something like this: =IF(ISERROR(VLOOKUP(A1,B1:C10,1,FALSE)),"",VLOOKUP (A1,B1:C10,1,FALSE)) This basically tells Excel: if the VLOOKUP does not return an EXACT match, return "" (instead of the #NA! error), else do the VLOOKUP and return the exact match. This is made simpler in XL 2007 with the new IFERROR function but my guess is you're not using that version of XL. Does this get at what you're looking for? Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: Excel wants to put an asterisk in the formula...see below =IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1) that makes the formula work, but it returns true/false instead of 0/1, i also checked enough of the records by hand to know a few of them are there, ie the formula should return "true", and the formula failed--all cells returned "false" But... lets say that i actually do use the vlookup function to look up a value in another column. the column i'm trying to search in contains a list of five digit numbers. if the number that i am searching for IS in that column, i want the Col_index_num value (which is a date) to be returned. well, that works fine for the numbers that ARE there, but the numbers that are NOT there, the formula still returns a date, only the date that it returns is in the row directly above where the number SHOULD BE. Therefore, my output is a bunch of dates, which is what i want, only i don't know which dates are correct (actually belong to a number that was found) and which dates are invalid. does this make sense? its pretty difficult to explain... -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
ok, i used the formula that you suggested, which sounded logical to me, but
it only appeared to work. Only, it returned all blanks, even in the cells that I knew it should have returned a value. i am using excel 2003 on microsoft xp. does it matter that the table that i'm looking up is in another worksheet within the same workbook? i don't think it should, but i'm just trying to think of what i'm doing wrong. i'll play around with it and dream of days when i get 2007... thanks dave -- nate "Dave F" wrote: Not sure why you would use an asterisk. Use: =IF(ISERROR(VLOOKUP(vlookupcriteria)),0,1) To your second point: it sounds like you are looking for an EXACT match, not an approximate match, and if that exact match is found you want it returned. Therefore, your VLOOKUP function should look something like this: =IF(ISERROR(VLOOKUP(A1,B1:C10,1,FALSE)),"",VLOOKUP (A1,B1:C10,1,FALSE)) This basically tells Excel: if the VLOOKUP does not return an EXACT match, return "" (instead of the #NA! error), else do the VLOOKUP and return the exact match. This is made simpler in XL 2007 with the new IFERROR function but my guess is you're not using that version of XL. Does this get at what you're looking for? Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: Excel wants to put an asterisk in the formula...see below =IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1) that makes the formula work, but it returns true/false instead of 0/1, i also checked enough of the records by hand to know a few of them are there, ie the formula should return "true", and the formula failed--all cells returned "false" But... lets say that i actually do use the vlookup function to look up a value in another column. the column i'm trying to search in contains a list of five digit numbers. if the number that i am searching for IS in that column, i want the Col_index_num value (which is a date) to be returned. well, that works fine for the numbers that ARE there, but the numbers that are NOT there, the formula still returns a date, only the date that it returns is in the row directly above where the number SHOULD BE. Therefore, my output is a bunch of dates, which is what i want, only i don't know which dates are correct (actually belong to a number that was found) and which dates are invalid. does this make sense? its pretty difficult to explain... -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
It shouldn't matter that the table is on another worksheet in the same
workbook. You may want to take a look here for some hints as to how to get VLOOKUP to work correctly: http://www.techonthenet.com/excel/formulas/vlookup.php . There are many other links relating to VLOOKUP he http://www.google.com/search?hl=en&q=vlookup+excel Take note of how the lookup table has to be structured. If you can't alter the structure of your lookup table to conform to VLOOKUP's requirements, it is likely possible that you can achieve the same thing with a combination of the INDEX/MATCH functions. If you determine that you can't use VLOOKUP then the best thing to do would be to respond with an explanation of how your lookup table is structured. Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: ok, i used the formula that you suggested, which sounded logical to me, but it only appeared to work. Only, it returned all blanks, even in the cells that I knew it should have returned a value. i am using excel 2003 on microsoft xp. does it matter that the table that i'm looking up is in another worksheet within the same workbook? i don't think it should, but i'm just trying to think of what i'm doing wrong. i'll play around with it and dream of days when i get 2007... thanks dave -- nate "Dave F" wrote: Not sure why you would use an asterisk. Use: =IF(ISERROR(VLOOKUP(vlookupcriteria)),0,1) To your second point: it sounds like you are looking for an EXACT match, not an approximate match, and if that exact match is found you want it returned. Therefore, your VLOOKUP function should look something like this: =IF(ISERROR(VLOOKUP(A1,B1:C10,1,FALSE)),"",VLOOKUP (A1,B1:C10,1,FALSE)) This basically tells Excel: if the VLOOKUP does not return an EXACT match, return "" (instead of the #NA! error), else do the VLOOKUP and return the exact match. This is made simpler in XL 2007 with the new IFERROR function but my guess is you're not using that version of XL. Does this get at what you're looking for? Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: Excel wants to put an asterisk in the formula...see below =IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1) that makes the formula work, but it returns true/false instead of 0/1, i also checked enough of the records by hand to know a few of them are there, ie the formula should return "true", and the formula failed--all cells returned "false" But... lets say that i actually do use the vlookup function to look up a value in another column. the column i'm trying to search in contains a list of five digit numbers. if the number that i am searching for IS in that column, i want the Col_index_num value (which is a date) to be returned. well, that works fine for the numbers that ARE there, but the numbers that are NOT there, the formula still returns a date, only the date that it returns is in the row directly above where the number SHOULD BE. Therefore, my output is a bunch of dates, which is what i want, only i don't know which dates are correct (actually belong to a number that was found) and which dates are invalid. does this make sense? its pretty difficult to explain... -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
This link is also useful: http://www.cpearson.com/excel/lookups.htm
Dave -- Brevity is the soul of wit. "Dave F" wrote: It shouldn't matter that the table is on another worksheet in the same workbook. You may want to take a look here for some hints as to how to get VLOOKUP to work correctly: http://www.techonthenet.com/excel/formulas/vlookup.php . There are many other links relating to VLOOKUP he http://www.google.com/search?hl=en&q=vlookup+excel Take note of how the lookup table has to be structured. If you can't alter the structure of your lookup table to conform to VLOOKUP's requirements, it is likely possible that you can achieve the same thing with a combination of the INDEX/MATCH functions. If you determine that you can't use VLOOKUP then the best thing to do would be to respond with an explanation of how your lookup table is structured. Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: ok, i used the formula that you suggested, which sounded logical to me, but it only appeared to work. Only, it returned all blanks, even in the cells that I knew it should have returned a value. i am using excel 2003 on microsoft xp. does it matter that the table that i'm looking up is in another worksheet within the same workbook? i don't think it should, but i'm just trying to think of what i'm doing wrong. i'll play around with it and dream of days when i get 2007... thanks dave -- nate "Dave F" wrote: Not sure why you would use an asterisk. Use: =IF(ISERROR(VLOOKUP(vlookupcriteria)),0,1) To your second point: it sounds like you are looking for an EXACT match, not an approximate match, and if that exact match is found you want it returned. Therefore, your VLOOKUP function should look something like this: =IF(ISERROR(VLOOKUP(A1,B1:C10,1,FALSE)),"",VLOOKUP (A1,B1:C10,1,FALSE)) This basically tells Excel: if the VLOOKUP does not return an EXACT match, return "" (instead of the #NA! error), else do the VLOOKUP and return the exact match. This is made simpler in XL 2007 with the new IFERROR function but my guess is you're not using that version of XL. Does this get at what you're looking for? Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: Excel wants to put an asterisk in the formula...see below =IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1) that makes the formula work, but it returns true/false instead of 0/1, i also checked enough of the records by hand to know a few of them are there, ie the formula should return "true", and the formula failed--all cells returned "false" But... lets say that i actually do use the vlookup function to look up a value in another column. the column i'm trying to search in contains a list of five digit numbers. if the number that i am searching for IS in that column, i want the Col_index_num value (which is a date) to be returned. well, that works fine for the numbers that ARE there, but the numbers that are NOT there, the formula still returns a date, only the date that it returns is in the row directly above where the number SHOULD BE. Therefore, my output is a bunch of dates, which is what i want, only i don't know which dates are correct (actually belong to a number that was found) and which dates are invalid. does this make sense? its pretty difficult to explain... -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
i have columns A through H. I am looking for 5 digit numbers in column A
from a different, shorter column on another worksheet. both the original column (column Z in worksheet 1) and column A in worksheet 2 are 5 digit numbers, but they are actually results from a different formula that i used to only use the last five digits in a longer number (RIGHT(Y,5), and RIGHT(B,5)), if that matters. column A is sorted in ascending order. column H contains a date associated with each number in the same row in column a. ideally, i would be able to return this date if and only if there is an exact match between the numbers in column Z and the numbers in column A. i don't care what i get returned if the numbers don't match, as long as it is not a date. column h is a "date obtained" column. my original list, in column z, is a list of part numbers that i want to check and see if i have already obtained, and knowing when i obtained them would just be a bonus (a very attainable bonus, so i thought). column a is a list of total part numbers that have been obtained to date. i want to be able to determine: given a list of part numbers, check the inventory table to see if and when those part numbers were obtained; if they are not on the inventory table, denote that by a "blank" or "N/A" or whatever, as long as it is not a date (a date would make it look like they were obtained already). now, i have tried to use the vlookup formula to return the date in column H. what is weird is that if i do not type "FALSE" in the "Range_lookup" field, the formula will return a date, only i know that date COULD be incorrect since excel isn't looking for an exact match. i took a few numbers from my original list and typed them in column a and made up fictitious dates in column h just to test my formula--it failed. i know this because when i do type in "FALSE" in range_lookup, all of the results are blank. not "N/A", just blank. -- nate "Dave F" wrote: This link is also useful: http://www.cpearson.com/excel/lookups.htm Dave -- Brevity is the soul of wit. "Dave F" wrote: It shouldn't matter that the table is on another worksheet in the same workbook. You may want to take a look here for some hints as to how to get VLOOKUP to work correctly: http://www.techonthenet.com/excel/formulas/vlookup.php . There are many other links relating to VLOOKUP he http://www.google.com/search?hl=en&q=vlookup+excel Take note of how the lookup table has to be structured. If you can't alter the structure of your lookup table to conform to VLOOKUP's requirements, it is likely possible that you can achieve the same thing with a combination of the INDEX/MATCH functions. If you determine that you can't use VLOOKUP then the best thing to do would be to respond with an explanation of how your lookup table is structured. Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: ok, i used the formula that you suggested, which sounded logical to me, but it only appeared to work. Only, it returned all blanks, even in the cells that I knew it should have returned a value. i am using excel 2003 on microsoft xp. does it matter that the table that i'm looking up is in another worksheet within the same workbook? i don't think it should, but i'm just trying to think of what i'm doing wrong. i'll play around with it and dream of days when i get 2007... thanks dave -- nate "Dave F" wrote: Not sure why you would use an asterisk. Use: =IF(ISERROR(VLOOKUP(vlookupcriteria)),0,1) To your second point: it sounds like you are looking for an EXACT match, not an approximate match, and if that exact match is found you want it returned. Therefore, your VLOOKUP function should look something like this: =IF(ISERROR(VLOOKUP(A1,B1:C10,1,FALSE)),"",VLOOKUP (A1,B1:C10,1,FALSE)) This basically tells Excel: if the VLOOKUP does not return an EXACT match, return "" (instead of the #NA! error), else do the VLOOKUP and return the exact match. This is made simpler in XL 2007 with the new IFERROR function but my guess is you're not using that version of XL. Does this get at what you're looking for? Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: Excel wants to put an asterisk in the formula...see below =IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1) that makes the formula work, but it returns true/false instead of 0/1, i also checked enough of the records by hand to know a few of them are there, ie the formula should return "true", and the formula failed--all cells returned "false" But... lets say that i actually do use the vlookup function to look up a value in another column. the column i'm trying to search in contains a list of five digit numbers. if the number that i am searching for IS in that column, i want the Col_index_num value (which is a date) to be returned. well, that works fine for the numbers that ARE there, but the numbers that are NOT there, the formula still returns a date, only the date that it returns is in the row directly above where the number SHOULD BE. Therefore, my output is a bunch of dates, which is what i want, only i don't know which dates are correct (actually belong to a number that was found) and which dates are invalid. does this make sense? its pretty difficult to explain... -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
it works if i TYPE the number in the formula, instead of using the cell
reference. like: vlookup(73061,Sheet2!A$2:H$2000,8,FALSE) instead of vlookup(Z17,Sheet2!A$2:H$2000,8,FALSE) i wonder why. this really doesn't help me since i would have to type the number that i want to look up in every single formula--that wouldn't be any different than using ctrl-f... -- nate "Dave F" wrote: It shouldn't matter that the table is on another worksheet in the same workbook. You may want to take a look here for some hints as to how to get VLOOKUP to work correctly: http://www.techonthenet.com/excel/formulas/vlookup.php . There are many other links relating to VLOOKUP he http://www.google.com/search?hl=en&q=vlookup+excel Take note of how the lookup table has to be structured. If you can't alter the structure of your lookup table to conform to VLOOKUP's requirements, it is likely possible that you can achieve the same thing with a combination of the INDEX/MATCH functions. If you determine that you can't use VLOOKUP then the best thing to do would be to respond with an explanation of how your lookup table is structured. Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: ok, i used the formula that you suggested, which sounded logical to me, but it only appeared to work. Only, it returned all blanks, even in the cells that I knew it should have returned a value. i am using excel 2003 on microsoft xp. does it matter that the table that i'm looking up is in another worksheet within the same workbook? i don't think it should, but i'm just trying to think of what i'm doing wrong. i'll play around with it and dream of days when i get 2007... thanks dave -- nate "Dave F" wrote: Not sure why you would use an asterisk. Use: =IF(ISERROR(VLOOKUP(vlookupcriteria)),0,1) To your second point: it sounds like you are looking for an EXACT match, not an approximate match, and if that exact match is found you want it returned. Therefore, your VLOOKUP function should look something like this: =IF(ISERROR(VLOOKUP(A1,B1:C10,1,FALSE)),"",VLOOKUP (A1,B1:C10,1,FALSE)) This basically tells Excel: if the VLOOKUP does not return an EXACT match, return "" (instead of the #NA! error), else do the VLOOKUP and return the exact match. This is made simpler in XL 2007 with the new IFERROR function but my guess is you're not using that version of XL. Does this get at what you're looking for? Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: Excel wants to put an asterisk in the formula...see below =IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1) that makes the formula work, but it returns true/false instead of 0/1, i also checked enough of the records by hand to know a few of them are there, ie the formula should return "true", and the formula failed--all cells returned "false" But... lets say that i actually do use the vlookup function to look up a value in another column. the column i'm trying to search in contains a list of five digit numbers. if the number that i am searching for IS in that column, i want the Col_index_num value (which is a date) to be returned. well, that works fine for the numbers that ARE there, but the numbers that are NOT there, the formula still returns a date, only the date that it returns is in the row directly above where the number SHOULD BE. Therefore, my output is a bunch of dates, which is what i want, only i don't know which dates are correct (actually belong to a number that was found) and which dates are invalid. does this make sense? its pretty difficult to explain... -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
Are the numbers in the cell references formatted as numbers or text? If
they're formatted as text your formula will not work. -- Brevity is the soul of wit. "nkc_esquire" wrote: it works if i TYPE the number in the formula, instead of using the cell reference. like: vlookup(73061,Sheet2!A$2:H$2000,8,FALSE) instead of vlookup(Z17,Sheet2!A$2:H$2000,8,FALSE) i wonder why. this really doesn't help me since i would have to type the number that i want to look up in every single formula--that wouldn't be any different than using ctrl-f... -- nate "Dave F" wrote: It shouldn't matter that the table is on another worksheet in the same workbook. You may want to take a look here for some hints as to how to get VLOOKUP to work correctly: http://www.techonthenet.com/excel/formulas/vlookup.php . There are many other links relating to VLOOKUP he http://www.google.com/search?hl=en&q=vlookup+excel Take note of how the lookup table has to be structured. If you can't alter the structure of your lookup table to conform to VLOOKUP's requirements, it is likely possible that you can achieve the same thing with a combination of the INDEX/MATCH functions. If you determine that you can't use VLOOKUP then the best thing to do would be to respond with an explanation of how your lookup table is structured. Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: ok, i used the formula that you suggested, which sounded logical to me, but it only appeared to work. Only, it returned all blanks, even in the cells that I knew it should have returned a value. i am using excel 2003 on microsoft xp. does it matter that the table that i'm looking up is in another worksheet within the same workbook? i don't think it should, but i'm just trying to think of what i'm doing wrong. i'll play around with it and dream of days when i get 2007... thanks dave -- nate "Dave F" wrote: Not sure why you would use an asterisk. Use: =IF(ISERROR(VLOOKUP(vlookupcriteria)),0,1) To your second point: it sounds like you are looking for an EXACT match, not an approximate match, and if that exact match is found you want it returned. Therefore, your VLOOKUP function should look something like this: =IF(ISERROR(VLOOKUP(A1,B1:C10,1,FALSE)),"",VLOOKUP (A1,B1:C10,1,FALSE)) This basically tells Excel: if the VLOOKUP does not return an EXACT match, return "" (instead of the #NA! error), else do the VLOOKUP and return the exact match. This is made simpler in XL 2007 with the new IFERROR function but my guess is you're not using that version of XL. Does this get at what you're looking for? Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: Excel wants to put an asterisk in the formula...see below =IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1) that makes the formula work, but it returns true/false instead of 0/1, i also checked enough of the records by hand to know a few of them are there, ie the formula should return "true", and the formula failed--all cells returned "false" But... lets say that i actually do use the vlookup function to look up a value in another column. the column i'm trying to search in contains a list of five digit numbers. if the number that i am searching for IS in that column, i want the Col_index_num value (which is a date) to be returned. well, that works fine for the numbers that ARE there, but the numbers that are NOT there, the formula still returns a date, only the date that it returns is in the row directly above where the number SHOULD BE. Therefore, my output is a bunch of dates, which is what i want, only i don't know which dates are correct (actually belong to a number that was found) and which dates are invalid. does this make sense? its pretty difficult to explain... -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
excel lookup functions
formatted as numbers, zero decimal spaces. is it because the numbers i'm
looking up are results of formulas? if so, then i am outta luck because i pretty much have to use the formula. the original list has a 3 digit constant prefix before the 5 digit number, the only problem is that sometimes there is a space between the constant and the number and sometimes there is not; and in the table that i am looking up the numbers in, there is always a space between the constant and the number; therefore if i use the lookup function with the original list, i won't get accurate results for the entries that don't have a space. -- nate "Dave F" wrote: Are the numbers in the cell references formatted as numbers or text? If they're formatted as text your formula will not work. -- Brevity is the soul of wit. "nkc_esquire" wrote: it works if i TYPE the number in the formula, instead of using the cell reference. like: vlookup(73061,Sheet2!A$2:H$2000,8,FALSE) instead of vlookup(Z17,Sheet2!A$2:H$2000,8,FALSE) i wonder why. this really doesn't help me since i would have to type the number that i want to look up in every single formula--that wouldn't be any different than using ctrl-f... -- nate "Dave F" wrote: It shouldn't matter that the table is on another worksheet in the same workbook. You may want to take a look here for some hints as to how to get VLOOKUP to work correctly: http://www.techonthenet.com/excel/formulas/vlookup.php . There are many other links relating to VLOOKUP he http://www.google.com/search?hl=en&q=vlookup+excel Take note of how the lookup table has to be structured. If you can't alter the structure of your lookup table to conform to VLOOKUP's requirements, it is likely possible that you can achieve the same thing with a combination of the INDEX/MATCH functions. If you determine that you can't use VLOOKUP then the best thing to do would be to respond with an explanation of how your lookup table is structured. Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: ok, i used the formula that you suggested, which sounded logical to me, but it only appeared to work. Only, it returned all blanks, even in the cells that I knew it should have returned a value. i am using excel 2003 on microsoft xp. does it matter that the table that i'm looking up is in another worksheet within the same workbook? i don't think it should, but i'm just trying to think of what i'm doing wrong. i'll play around with it and dream of days when i get 2007... thanks dave -- nate "Dave F" wrote: Not sure why you would use an asterisk. Use: =IF(ISERROR(VLOOKUP(vlookupcriteria)),0,1) To your second point: it sounds like you are looking for an EXACT match, not an approximate match, and if that exact match is found you want it returned. Therefore, your VLOOKUP function should look something like this: =IF(ISERROR(VLOOKUP(A1,B1:C10,1,FALSE)),"",VLOOKUP (A1,B1:C10,1,FALSE)) This basically tells Excel: if the VLOOKUP does not return an EXACT match, return "" (instead of the #NA! error), else do the VLOOKUP and return the exact match. This is made simpler in XL 2007 with the new IFERROR function but my guess is you're not using that version of XL. Does this get at what you're looking for? Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: Excel wants to put an asterisk in the formula...see below =IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1) that makes the formula work, but it returns true/false instead of 0/1, i also checked enough of the records by hand to know a few of them are there, ie the formula should return "true", and the formula failed--all cells returned "false" But... lets say that i actually do use the vlookup function to look up a value in another column. the column i'm trying to search in contains a list of five digit numbers. if the number that i am searching for IS in that column, i want the Col_index_num value (which is a date) to be returned. well, that works fine for the numbers that ARE there, but the numbers that are NOT there, the formula still returns a date, only the date that it returns is in the row directly above where the number SHOULD BE. Therefore, my output is a bunch of dates, which is what i want, only i don't know which dates are correct (actually belong to a number that was found) and which dates are invalid. does this make sense? its pretty difficult to explain... -- nate "Dave F" wrote: Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1) "If the VLOOKUP returns an error, then record 0, else record 1." Dave -- Brevity is the soul of wit. "nkc_esquire" wrote: how can i search a very long list of values to see if each individual value in a shorter list is in the long list? essentially, i am trying to prevent having to "ctrl-f" a thousand times...i don't need to return a value, like vlookup does, i just need to see if the numbers in one list are in the other list thanks, -- nate |
| All times are GMT +1. The time now is 07:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com