using vlookup in excel to compare columns

Question

hello members,

I have some data with close to 250 rows in a excel sheet(Excel 2003) that needs to be compared. Appreciate if someone can help me with vlookup / any other excel routine that can help me achieve this. How do i filter out failed results ?Some sample data from my excel sheet:

Condition to check:
Where $E = $Q
Check if rows ($F - $K) = rows($R to $w) 

Rows ( E - K )
1000xyz400100xyzA00680xyz0  19722.83 19565.7 157.13 11 2.74     11.00
1000xyz400100xyzA00682xyz0  7210.68  4111.53 3099.15 216.95  1.21    216.94
1000xyz430200xyzA00651xyz0  146.70  0.00 0.00 0.00 0.00 0.00

Rows (Q - W )
1000xyz400100xyzA00680xyz0  19722.83   19565.70 11.00 13.74 2.74 11.00
1000xyz400100xyzA00682xyz0  7210.68    4111.53 216.94 218.15 1.21 216.94

Another question is, do I need to perform a sort before I can check this or is vlookup able to handle unsorted data.

Answer 1

under the assumption of YES to both q1 and q2, I propose you use some (temporary) columns to validate your tables. The example shows what functions you would typically use for that kind of question:

column : L
heading: "FoundInQ"
formula: =NOT(ISNA(VLOOKUP(E2;$Q:$Q;1;FALSE)))
purpose: check if key found in E2 is present anywhere in column Q
comment: if Vlookup with the FALSE option doesn'g find an exact match, it returns #NA
         and we make use of that by specifically testing that using ISNA()
         E2 is the only relative address, so safe to copy this down your table

column : M
heading: "WhereInQ"
formula: =MATCH(E2;$Q:$Q;0)
purpose: get number of row in Q where key is found
comment: this we don't need directly, it is to illustrate how you get an index for a
         search key within a target range. Mind the parameter "0" at the end

column:  N
heading: "F equal R"
formula: =F2=INDEX($R:$R;M2)
comment: M2 makes use of the MATCH function above, here split for better clarity

Now you have some basic techniques of finding a key, getting an associated value etc. You can combine formulae like inserting the one of column M into the "M2" argument of formula N, let the whole be displayed conditionally on the result of column L, expand column N for more adjacent columns like in

=AND(F2=INDEX($R:$R;M2);G2=INDEX($S:$S;M2);H2=INDEX($T:$T;M2);...)

Needless to say that you should define an AutoFilter across these (temp) columns to seperate good from bad, and that the whole logic (looking from E to Q) can be reversed.

And No the two subtables need not be sorted.

That should solve ....

Good luck MikeD

Answer 2

First off, if you end up using vlookup for this as in the example I've given below, then you shouldn't need to sort as the Range_lookup value is set to FALSE which forces VLOOKUP to search for exact matches only, although if there are duplicates in your inital comparison rows E or Q then you could run into trouble with not finding all the instances of the matched data.

The formula below first checks to see if the first condition can be matched and then compares each of the 6 subsequent columns with their counterparts. This particular formula simply returns "Not Found" if no initial match, "All OK" if all rows match with their counterpart, or "Not OK" if one or more do not match. To get information on which field is notequal then you would need to break up the AND function to return column details.

=IF(ISNA(VLOOKUP(E1,$Q$1:$W$2,2,FALSE)),"Not Found",IF(AND(F1=VLOOKUP(E1,$Q$1:$W$2,2,FALSE),G1=VLOOKUP(E1,$Q$1:$W$2,3,FALSE),H1=VLOOKUP(E1,$Q$1:$W$2,4,FALSE),I1=VLOOKUP(E1,$Q$1:$W$2,5,FALSE),J1=VLOOKUP(E1,$Q$1:$W$2,6,FALSE),K1=VLOOKUP(E1,$Q$1:$W$2,7,FALSE)),"All OK","Not OK"))

To use it just drop it into a column adjacent to the set of data.