Hi - I am working on crossmatching information between two worksheets.

Does anyone know how to make the following changes to the statement
below -

1. Replace the return value = "Did not Match", to the actual
information in the contects of the cell that did not match.

2. All matched items return a result = "Match Found".

Right now, I'm getting the opposite result. Here's the formula that I
am working with -

=IF(ISERROR(VLOOKUP(BCR!A2,MKS!$A$2:$A$972,1,FALSE )),"Did not
Match",VLOOKUP(BCR!A2,MKS!$A$2:$A$972,1,FALSE))

Thanks!


--
mizzrizz
------------------------------------------------------------------------
mizzrizz's Profile: http://www.excelforum.com/member.php...o&userid=35515
View this thread: http://www.excelforum.com/showthread...hreadid=552969

Hi Mizzrizz,

Try this

=IF(ISERROR(VLOOKUP(BCR!A2,MKS!$A$2:$A$972,1,FALSE )),"Did not
Match",IF(VLOOKUP(BCR!A2,MKS!$A$2:$A$972,1,FALSE)= BCR!A2,"Match
Found"))

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=552969

Maybe this.......................

=IF(ISERROR(VLOOKUP(BCR!A2,MKS!$A$2:$A$972,1,FALSE )),BCR!A2,IF(VLOOKUP(BCR!A
2,MKS!$A$2:$A$972,1,FALSE)=BCR!A2,"Match Found"))

Vaya con Dios,
Chuck, CABGx3



"mizzrizz" wrote in
message ...

Hi - I am working on crossmatching information between two worksheets.

Does anyone know how to make the following changes to the statement
below -

1. Replace the return value = "Did not Match", to the actual
information in the contects of the cell that did not match.

2. All matched items return a result = "Match Found".

Right now, I'm getting the opposite result. Here's the formula that I
am working with -

=IF(ISERROR(VLOOKUP(BCR!A2,MKS!$A$2:$A$972,1,FALSE )),"Did not
Match",VLOOKUP(BCR!A2,MKS!$A$2:$A$972,1,FALSE))

Thanks!


--
mizzrizz
------------------------------------------------------------------------
mizzrizz's Profile:
http://www.excelforum.com/member.php...o&userid=35515
View this thread: http://www.excelforum.com/showthread...hreadid=552969

Thanks.. worked like a charm!

Is there any way to have the actual value of the cells for the one with
no match show instead of the words "Did not Match"?


--
mizzrizz
------------------------------------------------------------------------
mizzrizz's Profile: http://www.excelforum.com/member.php...o&userid=35515
View this thread: http://www.excelforum.com/showthread...hreadid=552969

Or the somewhat shorter

=IF(ISNUMBER(MATCH(BCR!A2,MKS!$A$2:$A$972,0)),"Mat ch found","Did not match")

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"VBA Noob" wrote in
message ...

Hi Mizzrizz,

Try this

=IF(ISERROR(VLOOKUP(BCR!A2,MKS!$A$2:$A$972,1,FALSE )),"Did not
Match",IF(VLOOKUP(BCR!A2,MKS!$A$2:$A$972,1,FALSE)= BCR!A2,"Match
Found"))

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=552969

or use a array.

{=IF(OR(BCR!A2=MKS!A:A),"Match Found",BCR!A2)}

Use ctrl + shift + enter to add the brackets "{}"

Note of warning - If BCR!A2 is blank then it returns Match Found.

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=552969

Thanks.. I'm getting closer!

I'd like to take this one step further.. right now the following
formula does a straight column to column compare -

=IF(ISNUMBER(MATCH(B2,$E$2:$E$972,0)),"Match Found","No Match")

But, what if I would like to include additional criteria in the
validation? For instance, I want first to look for a match on B2.. then
check C2 against E2 and F2.

Making sense? Let me know if I need to provide better info.. and thanks
for the help so far.. :)


--
mizzrizz
------------------------------------------------------------------------
mizzrizz's Profile: http://www.excelforum.com/member.php...o&userid=35515
View this thread: http://www.excelforum.com/showthread...hreadid=552969

Do you mean test for a match with any of the values in B2:F2?


=IF(SUMPRODUCT(--(ISNUMBER(MATCH(BCR!B2:F2,MKS!$A$2:$A$972,0))))0, "Match
found","Did not match")



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"mizzrizz" wrote in
message ...

Thanks.. I'm getting closer!

I'd like to take this one step further.. right now the following
formula does a straight column to column compare -

=IF(ISNUMBER(MATCH(B2,$E$2:$E$972,0)),"Match Found","No Match")

But, what if I would like to include additional criteria in the
validation? For instance, I want first to look for a match on B2.. then
check C2 against E2 and F2.

Making sense? Let me know if I need to provide better info.. and thanks
for the help so far.. :)


--
mizzrizz
------------------------------------------------------------------------
mizzrizz's Profile:
http://www.excelforum.com/member.php...o&userid=35515
View this thread: http://www.excelforum.com/showthread...hreadid=552969

Hi.. I'm looking for results that would show

if B2 and C2 match E2 and F2 then - Match Found

It becomes a two column to two column compare instead of the first
statement we worked on which was only a single to single (B2 to E2).

A "Did not Match" result would be if let say that B2 and E2 matched,
but their counterparts (C2 and F2 did not)


--
mizzrizz
------------------------------------------------------------------------
mizzrizz's Profile: http://www.excelforum.com/member.php...o&userid=35515
View this thread: http://www.excelforum.com/showthread...hreadid=552969

I'm attaching my file if it helps to see what I'm referring to. Look at
the second worksheet.... it should already be in focus when the file is
opened.


--
mizzrizz
------------------------------------------------------------------------
mizzrizz's Profile: http://www.excelforum.com/member.php...o&userid=35515
View this thread: http://www.excelforum.com/showthread...hreadid=552969