Match, Index, Vlookup, If (confused)

P

PJS

Hi everyone.

I am not sure which commands to use, can someone please show me how to
accomplish this?

I have two different worksheets

Worksheet 1 Worksheet 2
Name Total Name Total
Abe 3 John
Bob 4 Jane
Charlie 5 Bob

I would like to compare the names in column A (worksheet 1) vs. the names in
colum A (worksheet 2), if they match, in this case Bob, return the value 4 in
worksheet 2. For John and Jane, since no matches are found, return the value
0.

Is this possible and what commands are needed?

Thank you,

Peter
 
D

Damon Longworth

The Vlookup will return the values needed, but will return an NA if not
found. The Vlookup can be wrapped in an IF(IsNa to get your 0 if not found:

=if(isna(vlookup()),0,vlookup())

--
Damon Longworth

2006 East Coast Excel User Conference
April 19/21st, 2006
Holiday Inn, Boardwalk
Atlantic City, New Jersey
Early Bird Registration Now Open!!
www.ExcelUserConference.com

2006 UK Excel User Conference
Summer, 2006
London, England
Hi everyone.

I am not sure which commands to use, can someone please show me how to
accomplish this?

I have two different worksheets

Worksheet 1 Worksheet 2
Name Total Name Total
Abe 3 John
Bob 4 Jane
Charlie 5 Bob

I would like to compare the names in column A (worksheet 1) vs. the names in
colum A (worksheet 2), if they match, in this case Bob, return the value 4
in
worksheet 2. For John and Jane, since no matches are found, return the value
0.

Is this possible and what commands are needed?

Thank you,

Peter
 
G

Govind

Hi,

Use

=IF(ISERROR((VLOOKUP(A2,Sheet1!$A$2:$B$5,2,FALSE))),0,(VLOOKUP(A2,Sheet1!$A$2:$B$5,2,FALSE)))


and copy it down.

Here A2 is the cell where 'John' is in Worksheet 2.

Regards

Govind.
 
P

PJS

Thank you very very much for the formula!


Govind said:
Hi,

Use

=IF(ISERROR((VLOOKUP(A2,Sheet1!$A$2:$B$5,2,FALSE))),0,(VLOOKUP(A2,Sheet1!$A$2:$B$5,2,FALSE)))


and copy it down.

Here A2 is the cell where 'John' is in Worksheet 2.

Regards

Govind.
Click to expand...
 
Top