i'm working on a long (approx 5000 lines) data sheet and i have a different
list containing erroneous values that relate to a particular row in the 1st
data sheet. i would need to run a function which gives me say a true/false
next to each cell in this particular row wherever it finds a matching value
from the second list of errors. more specifically, say i have the value '1'
in the first data sheet and i have '1', '2', '3', '4' as erroneuos values in
the error list, therefore i would like to see TRUE next to this cell, and say
i have '5' in the cell then i would like to see FALSE in the cell next to it
(since '5' is not listed as an erroneous value in the 2nd list). well, hope
this makes sense. i have been trying to create a multi-function for this
using IF, VLOOKUP etc. without success and it's extremely time consuming to
delete each erroneous value manually. any help is appreciated

One guess ..
Assuming source data in A2 down, place this in B2:
=IF(A2="","",ISNUMBER(MATCH(A2,{1;2;3;4},0)))
Copy down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Daniel" wrote:
> i'm working on a long (approx 5000 lines) data sheet and i have a different
> list containing erroneous values that relate to a particular row in the 1st
> data sheet. i would need to run a function which gives me say a true/false
> next to each cell in this particular row wherever it finds a matching value
> from the second list of errors. more specifically, say i have the value '1'
> in the first data sheet and i have '1', '2', '3', '4' as erroneuos values in
> the error list, therefore i would like to see TRUE next to this cell, and say
> i have '5' in the cell then i would like to see FALSE in the cell next to it
> (since '5' is not listed as an erroneous value in the 2nd list). well, hope
> this makes sense. i have been trying to create a multi-function for this
> using IF, VLOOKUP etc. without success and it's extremely time consuming to
> delete each erroneous value manually. any help is appreciated

Use this:

=IF(ISNA(VLOOKUP(Your Lookup Value,The range of error values - 1 to 4 in
your example,1,FALSE)),FALSE,TRUE)

Basically, if the Vlookup doesn't find your value in the range of error
values (e.g. it won't find 5 in your 1-4 example), then the ISNA is true
(i.e. Vlookup returns a #N/A) and so the IF statement returns a FALSE,
otherwise, we found a match and it returns TRUE.

"Daniel" wrote:

> i'm working on a long (approx 5000 lines) data sheet and i have a different
> list containing erroneous values that relate to a particular row in the 1st
> data sheet. i would need to run a function which gives me say a true/false
> next to each cell in this particular row wherever it finds a matching value
> from the second list of errors. more specifically, say i have the value '1'
> in the first data sheet and i have '1', '2', '3', '4' as erroneuos values in
> the error list, therefore i would like to see TRUE next to this cell, and say
> i have '5' in the cell then i would like to see FALSE in the cell next to it
> (since '5' is not listed as an erroneous value in the 2nd list). well, hope
> this makes sense. i have been trying to create a multi-function for this
> using IF, VLOOKUP etc. without success and it's extremely time consuming to
> delete each erroneous value manually. any help is appreciated

Wow BobT!! thank you, you saved me about half a days work! it did the work
perfectly. Many thanks
fyi, here's what i ended up using
=IF(ISNA(VLOOKUP(A1,$F$2:$F$52,1,FALSE)),FALSE,TRUE)

"BobT" wrote:

> Use this:
>
> =IF(ISNA(VLOOKUP(Your Lookup Value,The range of error values - 1 to 4 in
> your example,1,FALSE)),FALSE,TRUE)
>
> Basically, if the Vlookup doesn't find your value in the range of error
> values (e.g. it won't find 5 in your 1-4 example), then the ISNA is true
> (i.e. Vlookup returns a #N/A) and so the IF statement returns a FALSE,
> otherwise, we found a match and it returns TRUE.
>
> "Daniel" wrote:
>
> > i'm working on a long (approx 5000 lines) data sheet and i have a different
> > list containing erroneous values that relate to a particular row in the 1st
> > data sheet. i would need to run a function which gives me say a true/false
> > next to each cell in this particular row wherever it finds a matching value
> > from the second list of errors. more specifically, say i have the value '1'
> > in the first data sheet and i have '1', '2', '3', '4' as erroneuos values in
> > the error list, therefore i would like to see TRUE next to this cell, and say
> > i have '5' in the cell then i would like to see FALSE in the cell next to it
> > (since '5' is not listed as an erroneous value in the 2nd list). well, hope
> > this makes sense. i have been trying to create a multi-function for this
> > using IF, VLOOKUP etc. without success and it's extremely time consuming to
> > delete each erroneous value manually. any help is appreciated

> =IF(ISNA(VLOOKUP(A1,$F$2:$F$52,1,FALSE)),FALSE,TRUE)

If it's just a single col check ..
this adaptation of my earlier suggestion should work as well:
=IF(A1="","",ISNUMBER(MATCH(A1,$F$2:$F$52,0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---