VLOOKUP and IF together

[Danyal]

Hello everyone. this is my first post here. i've had some diffculty implementing some functions and was hoping some of the veterans would be able to help me out here.

1) column A has a list of, say, 5000 number values (row 3 to 5003).
2) column C to N (12 columns) have a list of of values that are all comparing values from 12 different files (1 for each column) to the values in column A. non-matches of course, are displayed as #NA.

what i would like to do is:
1) in column B, implement an IF function (if appropriate) and see whether the number values from column A actually exist in columns C to N (i.e they were successfully matched via Vlookup). If the value exists in columns C to N, i would like this column to return either the value itself or return "True" (or any word really).

would the IF function be appropriate for something like this or would you recommend another function and any ideas how i would set it up?

with the IF function my biggest hassle is that i can't seem to implement more then 7 IF functions and that the #NA value that vlookup returns in the case of an unsuccessful match for a particular column messes up the entire formula.

many thanks for any help .

[NBVC]

Try this formula in B3:

=OR(ISNUMBER(SEARCH("#"&A3&"#","#"&C3:N3&"#")))

Confirm the formula with CTRL+SHIFT+ENTER not just ENTER....

If confirmed correctly you will see {} brackets appear around the formula.

Copy the formula down

If you want to return a blank if the value doesn't exist, then enclose the formula in an IF() formula....

e.g. =IF(OR(ISNUMBER(SEARCH("#"&A3&"#","#"&C3:N3&"#"))),TRUE,"") again confirmed with CSE key combo.

[starguy]

Hello everyone. this is my first post here. i've had some diffculty implementing some functions and was hoping some of the veterans would be able to help me out here.

1) column A has a list of, say, 5000 number values (row 3 to 5003).
2) column C to N (12 columns) have a list of of values that are all comparing values from 12 different files (1 for each column) to the values in column A. non-matches of course, are displayed as #NA.

what i would like to do is:
1) in column B, implement an IF function (if appropriate) and see whether the number values from column A actually exist in columns C to N (i.e they were successfully matched via Vlookup). If the value exists in columns C to N, i would like this column to return either the value itself or return "True" (or any word really).

would the IF function be appropriate for something like this or would you recommend another function and any ideas how i would set it up?

with the IF function my biggest hassle is that i can't seem to implement more then 7 IF functions and that the #NA value that vlookup returns in the case of an unsuccessful match for a particular column messes up the entire formula.

many thanks for any help .

Hi

try following formula in B2 and copy down.

=IF(AND(ISERROR(MATCH(A2,C:C,0)),ISERROR(MATCH(A2,D:D,0)),ISERROR(MATCH(A2,E:E,0)),ISERROR(MATCH(A2,F:F,0)),ISERROR(MATCH(A2,G:G,0)),ISERROR(MATCH(A2,H:H,0)),ISERROR(MATCH(A2,I:I,0)),ISERROR(MATCH(A2,J:J,0)),ISERROR(MATCH(A2,K:K,0)),ISERROR(MATCH(A2,L:L,0)),ISERROR(MATCH(A2,M:M,0)),ISERROR(MATCH(A2,N:N,0)))=TRUE,"not","found")

it will return "found" if value in corresponding cell of col A is found in any cell of columns C to N otherwise "not".

hope this would help.

[Danyal]

many thanks to the both of you . i tried starguys formula first and it worked perfectly!

will have to ponder the logic of these formulas for a while before i can start using them regularly. regardless, thank you very much.

[starguy]

many thanks to the both of you . i tried starguys formula first and it worked perfectly!

will have to ponder the logic of these formulas for a while before i can start using them regularly. regardless, thank you very much.

you're welcome and thank you for the feedback.
pleased to know that your problem is solved.

[Danyal]

hey i had one other question and i thought i'd post it here since its related to the original query.

now that i know which files are matched and which aren't, i would like to extract this data (the cell values from column A plus one more column) and have it displayed in another file in another column. however i would like to only extract and display the files that have not been matched (i.e i don't want the files which were 'found' to be displayed in the new file).

additionally i was using the COUNTIF function to count and display how many cells did not match. hwever, the problem with COUNTIF is that it gives a '#####' error unless i open all files containing the data individually (it picks up the values automatically then). my question is that would there be a way to implement it so that i can count how many cells in column B are showing as not found without having to open all the files containing this data individually?

as always, many thanks for any help .

[starguy]

hey i had one other question and i thought i'd post it here since its related to the original query.

now that i know which files are matched and which aren't, i would like to extract this data (the cell values from column A plus one more column) and have it displayed in another file in another column. however i would like to only extract and display the files that have not been matched (i.e i don't want the files which were 'found' to be displayed in the new file).

additionally i was using the COUNTIF function to count and display how many cells did not match. hwever, the problem with COUNTIF is that it gives a '#####' error unless i open all files containing the data individually (it picks up the values automatically then). my question is that would there be a way to implement it so that i can count how many cells in column B are showing as not found without having to open all the files containing this data individually?

as always, many thanks for any help .

you should use COUNTIF function in the original files which contains data. e.g if you have two files A and B which are closed and you use COUNTIF formula in file C to count entries which are in closed files A and B.
rather you should use COUNTIF function in any cell of files A and B then make a link in file C to those cells of file A and B and in file C use following code.

see the following link.
http://j-walk.com/ss/excel/tips/tip82.htm

this link may also of use
http://www.dailydoseofexcel.com/arch...sed-workbooks/