equivalent functions of an outer join in Excel? (urgent)

Hi,

Here's what I need to do:

I have 2 lists/tables as shown below:
Table X:
NAME      TOTAL_COUNT
a      546
b      249
c      605
d      10
e      549
f      1204
g      1269

Table Y:
NAME      SUCCESS_COUNT
a      3
b      2
c      5

I want to join the 2 tables so that if a "NAME" appear in Table X but not in Table Y, it will be listed in Table Y with a value of "0" for "SUCCESS_COUNT".

It's easy to accomplish sth like this in SQL with an outer join. Is there a way to do it in Excel(maybe with a if-then statment of some sort?) I'm not familiar with Excel programing,hope somebody could help me out.

Thanks a lot
-Qianq
qianqAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrt1Commented:
I think you probably need to use the VLOOKUP function:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value is the value you're looking for (e.g. the first cell in table X)

table_array is the table you're searching in - i.e. table Y

col_index_num is the column in table Y that you're searching in ("1" in this instance).

range_lookup should be set to false - you're only interested in an exact match.

The VLOOKUP function will return an error #N/A if the value isn't found, so yuo can use something like this to handle the error:

 = IF ISERROR(VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),"NOT FOUND","FOUND")
0
tureCommented:
Qianq,

With first list in A1:B8 on Sheet1 and second list in A1:B4 on Sheet2, do this:

1. Enter the header 'Success' in C1 on Sheet1
2. Enter this formula in cell C2 on Sheet1:
=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE)),0,VLOOKUP(A2,Sheet2!A:B,2,FALSE))
3. Copy the formula from cell C2 all the way down to C8

4. If you want to get rid of the formulas and keep just the values in column C on Sheet1 do this:
4.1 Select and copy C2:C8 on Sheet1
4.2 Edit - Paste Special - Values - OK
4.3 Press Esc to turn off the blinking

Done!

Ture Magnusson
Karlstad, Sweden
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qianqAuthor Commented:
True,

Thanks a lot for your help. It worked beautifully!

Mrt1, thank you too, your answer is along the same line...

-Qianq
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.