VLOOKUP query

[quetzalc0atl]

hello gods of excel,

I am trying create a vlookup query where by if data is matching in
column a1 on worksheet1 with column a1 on worksheet2, it then looks to
see if column b2 on worksheet1 is matching with column b2 on worksheet2
then returns a "match" or "not matching" value as text.

I can perform this on one column but not sure how to fiddle with the
formula to expand it too search the second column.

I'm using:

=IF(ISNA(VLOOKUP(Sheet1!$A$2:$A$14,Sheet2!$A$2:$A$14,1,0)),"not
matching","matching")

any tips will be greatly appreciated,

thanks,

Clive

 

[vezerid]

=IF(ISNUMBER(MATCH(Sheet1!A2,Sheet2!$A$2:$A$14,0)),IF(ISNUMBER(MATCH(Sheet1!A2,Sheet2!$A$2:$A$14,0)),"match","no
match"))

HTH
Kostis Vezerides

 

[Guest]

This is quite simple. Copy you formula to the cell and which you want your
answer to be displayed and then, Change you formula cell references to B, as
follows:
=IF(ISNA(VLOOKUP(Sheet1!$B$2:$B$14,Sheet2!$B$2:$B$14,1,0)),"not
matching","matching")

If you want this to change automatically then remove the $ sign from the
fomula so the reference cahnges, this work well when you are copying this
formula down rows, If you do this then it should look like this:
=IF(ISNA(VLOOKUP(Sheet1!$B2:$B14,Sheet2!$B2:$B14,1,0)),"not
matching","matching")

 

[quetzalc0atl]

thank you, but unfortunately that only seems to return a match if the
value in the column b is presernt anywhere in the range of column b on
worksheet2.

here is some sample data:

Name Application
G.Anderson VB6
G.Anderson Team Spirit
B.Jones MapManager/Editor
A.Smith Groupwise 5.5

assuming name is column A and Application is column B. these are like
records so to speak. So where a name appears in column A on worksheet
one I need to check to see if it appears in worksheet two. And if it
does then see if one of the application name's that is next to it in
worksheet 1 column b also appears next to it on worksheet 2 column b.
Been trying to do this for 4 days now and all i get is errors :'(.

 

[vezerid]

Let us say G.Anderson appears in K1 of Sheet2. Now you want to see if
the combination G.Anderson with any of the Applications appearing next
to G.Anderson in Sheet2 also appears in Sheet1?

Try the following *array* formula (commit with Shift+Ctrl+Enter)

=IF(ISNUMBER(MATCH(K1&$B$2:$B$14,Sheet1!$A$2:$A$14&Sheet1!$B$2:$B$14,0)),"match","no
match")

Does this help?
Kostis Vezerides

 

[quetzalc0atl]

haha no it seems to match 1 or two then returns no match for everything
else. thank you for your suggestions. I think if i appraoch it as
comparing records, using name as a unique identifier then checking to
see what are the unique iterations of it between the two worksheets.
I'll get there in the end I am hoping.

 

[quetzalc0atl]

Ok I have sort of solved this problem and thought I would share the
love so to speak. What I did eventually was merge the 2 datain the
columns in worksheet1 in the next column using something like
=A2&" "&B2
if you don't want spaces use =A2&B2
I did the same on the worksheet two and then used this formula to look
for duplicates
=IF(ISNA(VLOOKUP(C2,Sheet4!$C$2:$C$14,1,0)),"no match", "match")

hope this helps others.