VLOOKUP Help Needed

M

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!
 
V

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
 
C

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
 
M

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

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
 
V

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
 
M

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.. :)
 
P

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
 
M

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):eek:
 
M

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