Excel Compare

Originally Posted by starguy

here is file with formula that returns what you required

Hello Starguy

"If the text of the any cell on D column appears in any of the three previous columns (A, B, C), I need that text on the Fifth column"

If he wants the text in D to be in E isn't it

=IF(AND(ISERROR(MATCH(D1,$A$1:$A$20,0)),ISERROR(MATCH(D1,$B$1:$B$20,0)),ISERROR(MATCH(D1,$C$1:$C$20,0))),D1,"")

Hi
Starguy

Thanks That is perfectly what I want.

How can I delete empty row from column E and arrange text alphabatically.

Thanks again for your help.

Originally Posted by dayaptl

Hi
Starguy

Thanks That is perfectly what I want.

How can I delete empty row from column E and arrange text alphabatically.

Thanks again for your help.

Select column E, copy > paste special > values, then select A1:E20, go to Data, Sort, Ascending, all the blanks will be at the top with the rest sorted alphabetically

Originally Posted by oldchippy

Hello Starguy

=IF(AND(ISERROR(MATCH(D1,$A$1:$A$20,0)),ISERROR(MATCH(D1,$B$1:$B$20,0)),ISERROR(MATCH(D1,$C$1:$C$20,0))),D1,"")

Can the above formula changed like this ?

=IF(AND(ISERROR(MATCH(D1,$A$1:$A$20,FALSE)),ISERROR(MATCH(D1,$B$1:$B$20,FALSE)),ISERROR(MATCH(D1,$C$1:$C$20,FALSE))),D1,"")

Should it provide the same result ? I have tried it but I was getting some problem in that? Can you please clarify??

Originally Posted by Dhruva101

Can the above formula changed like this ?



Should it provide the same result ? I have tried it but I was getting some problem in that? Can you please clarify??

Hi Dhruva101,

This is an extract from Excel Help on the MATCH function, the match_type must be a number as seen below not TRUE or FALSE

MATCH(lookup_value,lookup_array,match_type)

Lookup_value is the value you use to find the value you want in a table.

Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.

Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1.

Remarks

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
If match_type is 0 and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Hope this answers your question

oldchippy

Originally Posted by Dhruva101

Can the above formula changed like this ?



Should it provide the same result ? I have tried it but I was getting some problem in that? Can you please clarify??

=IF(AND(ISERROR(MATCH(D1,$A$1:$A$20,0)),ISERROR(MATCH(D1,$B$1:$B$20,0)),ISERROR(MATCH(D1,$C$1:$C$20,0))),"",D1)

I have coloured formula to understand its different parts
part 1 (blue) finds value of D1 in A1:A20 and if value is not found ISERROR will return TRUE, if value is found ISERROR will return FALSE.

in the same way second part (red) finds value of D1 in B1:B20 an if not found ISERROR will return TRUE, if value is found ISERROR will return FALSE.

third part (green) finds value in C1:C20 if not found ISERROR will return TRUE, if value is found ISERROR will return FALSE.

AND function will return TRUE if above all three parts return TRUE and AND function will return FALSE if any of above parts returns FALSE.

finally IF function will return "" (blank) if AND function returns TRUE and value of D1 if AND function will return FALSE.

if you change the formula as you mentioned the resulting column will show those values which are found in any of previous ranges.

hope this will help you understand what that formula is.

Regards.