• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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
0
qianq
Asked:
qianq
1 Solution
 
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
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now