SOLVED

# Compare two sheets

Copper Contributor

# Compare two sheets

Sheet 1

Pc1

Pc1

Sheet 2

Pc1.test.com

Pc3.test7.com

How to compare from sheet 1 to 2.

I tried with vlookup but not working.

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Compare two sheets

MATCH is probably best for comparison, but VLOOKUP can do it, too. Whichever you pick, you'll need a wild card. The wildcard is added before and after the lookup value so MATCH or VLOOKUP will locate the "pc1" anywhere in the string.

MATCH:

``=MATCH("*"&A1&"*",Sheet2!\$A\$1:\$A\$2,0)``

VLOOKUP:

``=VLOOKUP("*"&A1&"*",Sheet2!\$A\$1:\$A\$2,1,0)``

# Re: Compare two sheets

MATCH formula showing return 1,2 results. But vlookup working fine. What i except.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Compare two sheets

MATCH is probably best for comparison, but VLOOKUP can do it, too. Whichever you pick, you'll need a wild card. The wildcard is added before and after the lookup value so MATCH or VLOOKUP will locate the "pc1" anywhere in the string.

MATCH:

``=MATCH("*"&A1&"*",Sheet2!\$A\$1:\$A\$2,0)``

VLOOKUP:

``=VLOOKUP("*"&A1&"*",Sheet2!\$A\$1:\$A\$2,1,0)``