Mulitple conditonal match / Vlookup
 

Hi I have 2 sheets.

On one sheet I have first and last name and other information.
On sheet 2 I have first and last name and other information.

But, sheet 2 does not have all the ppl from sheet 1. ie some are missing.

What I want to do is on sheet 1, identify who is not on sheet 2.

I have tried vlookup and match but can't seem to get it right.

The problem of course is that the last name can be repeated and the first
name can be repeated but not the combination of both.

Hi!

With names in column A of both sheets, Sheet1 A1:A100 and Sheet2 A1:A50

In Sheet1 B1 enter this formula:

=IF(COUNTIF(Sheet2!A$1:A$50,A1),"","Not on Sh 2")

Biff

"Madduck" wrote in message
...
Hi I have 2 sheets.

On one sheet I have first and last name and other information.
On sheet 2 I have first and last name and other information.

But, sheet 2 does not have all the ppl from sheet 1. ie some are missing.

What I want to do is on sheet 1, identify who is not on sheet 2.

I have tried vlookup and match but can't seem to get it right.

The problem of course is that the last name can be repeated and the first
name can be repeated but not the combination of both.

Easiest to insert a column next to each table and concatenate your first and
last names (=A1&A2 will combine the values in A1 and A2 to form a single
string). Then you could use Vlookup or Match using your concatenated columns.

Alternatively, you can use the MATCH function as an array formula.

For example, if I have first names in B1:B3, last name in C1:C3, first name
in E1:E3, last name in F1:F3 and wanted to match the names in column B&C to
the names in E and F, I'd enter the following in cell A1. When you type in
the formula, don't press enter, but press Control+Shift+Enter - this will
enter it as an array formula. Then copy the formula for the rest of column A
- the missing names will show as "N/A"

=MATCH(B1&C1,E$1:E$3&$F$1:F$3,0)

"Madduck" wrote:

Hi I have 2 sheets.

On one sheet I have first and last name and other information.
On sheet 2 I have first and last name and other information.

But, sheet 2 does not have all the ppl from sheet 1. ie some are missing.

What I want to do is on sheet 1, identify who is not on sheet 2.

I have tried vlookup and match but can't seem to get it right.

The problem of course is that the last name can be repeated and the first
name can be repeated but not the combination of both.

Thanks JBM , I was able to adapt this easliy.

"JMB" wrote:

Easiest to insert a column next to each table and concatenate your first and
last names (=A1&A2 will combine the values in A1 and A2 to form a single
string). Then you could use Vlookup or Match using your concatenated columns.

Alternatively, you can use the MATCH function as an array formula.

For example, if I have first names in B1:B3, last name in C1:C3, first name
in E1:E3, last name in F1:F3 and wanted to match the names in column B&C to
the names in E and F, I'd enter the following in cell A1. When you type in
the formula, don't press enter, but press Control+Shift+Enter - this will
enter it as an array formula. Then copy the formula for the rest of column A
- the missing names will show as "N/A"

=MATCH(B1&C1,E$1:E$3&$F$1:F$3,0)

"Madduck" wrote:

Hi I have 2 sheets.

On one sheet I have first and last name and other information.
On sheet 2 I have first and last name and other information.

But, sheet 2 does not have all the ppl from sheet 1. ie some are missing.

What I want to do is on sheet 1, identify who is not on sheet 2.

I have tried vlookup and match but can't seem to get it right.

The problem of course is that the last name can be repeated and the first
name can be repeated but not the combination of both.