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

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


Here's what I need to do:

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

Table Y:
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
1 Solution
I think you probably need to use the VLOOKUP function:


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")

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:
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


Ture Magnusson
Karlstad, Sweden
qianqAuthor Commented:

Thanks a lot for your help. It worked beautifully!

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


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