VLOOKUP Help Needed

[mizzrizz]

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!

 

[VBA Noob]

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

 

[CLR]

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]

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

 

[Peo Sjoblom]

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]

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

 

[mizzrizz]

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

 

[Peo Sjoblom]

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]

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]

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.