VLOOKUP Help Needed

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!

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

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"?

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Or the somewhat shorter

=IF(ISNUMBER(MATCH(BCR!A2,MKS!$A$2:$A$972,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


"VBA Noob" <[email protected]> wrote in
message news:[email protected]...
>
> 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

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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)