comparing data in different rows

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

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.
 
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)
 
=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
---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
Absoute Cell Ref Changes When Inserting New Row in Referenced Shee 8
Losing rows that reference data from another worsheet 1
using the =page! feature to transpose a row to column 0
Selecting rows. 3
Sum values in one row dependent on a value in a different row 4
Excel VBA - counting cells by colors and names to it in two different rows. 0

Back
Top