3

I've found this excel add-in to buy that appears to do what I need, but I'd rather have code that's open to use as I wish. While a GUI is nice, it's not required.

In an attempt to make the question more clear, I'm adding some two sample "input" tables in tab delimited form, and the resulting output table:

SAMPLE_INPUT_TABLE_01

horse<tab>age<tab>body
a<tab>1<tab>A
b<tab>2<tab>B
b<tab>3<tab>C
c<tab>4<tab>D
e<tab>5<tab>E
f<tab>6<tab>F

SAMPLE_INPUT_TABLE_02

rider<tab>horse<tab>finish<tab>date
xxx<tab>a<tab>yy<tab>zz
xxx<tab>b<tab>yy<tab>zz
xxx<tab>b<tab>yy<tab>zz
xxx<tab>c<tab>yy<tab>zz
xxx<tab>e<tab>yy<tab>zz
xxx<tab>f<tab>yy<tab>zz

SAMPLE_OUTPUT_TABLE_03

rider<tab>horse<tab>finish<tab>date<tab>age<tab>body
xxx<tab>a<tab>yy<tab>zz<tab>1<tab>A
xxx<tab>b<tab>yy<tab>zz<tab>3<tab>C
xxx<tab>b<tab>yy<tab>zz<tab>3<tab>C
xxx<tab>c<tab>yy<tab>zz<tab>4<tab>D
xxx<tab>e<tab>yy<tab>zz<tab>5<tab>E
xxx<tab>f<tab>yy<tab>zz<tab>6<tab>F

If it matters, I'm using Office 2010 on Windows 7.

Improve this question
3
  • I don't see a question here except for the one in the subject. What exactly are you asking, except for people to go visit the web site for the add-in?
    – Ken White
    Commented Dec 29, 2010 at 19:20
  • @Ken White: The question is the question, and the link is to provide info/proof on what is possible. Since it appears you read both the question and visited the link, what is not clear? Thanks
    – blunders
    Commented Dec 29, 2010 at 19:23
  • @Ken White: Added two sample tables for input, and a sample output table -- which should make the question more clear, let me know if that helps. Thanks.
    – blunders
    Commented Dec 29, 2010 at 19:48

2 Answers 2

Reset to default
7

I think this emulates the example in your link:

alt text

The formulas are:

In I2 (and copied down): 

=VLOOKUP(F2,$A$2:$C$7,2)

In J2 (and copied down: 

=VLOOKUP(F2,$A$2:$C$7,3)
Improve this answer
4
  • (+1 and selected as answer) -- Thanks for posting working code, updated my sample data to match your answer; since I know you where creating your sample at the same time I added mine. Cheers!
    – blunders
    Commented Dec 29, 2010 at 20:14
  • 1
    @blunders Tnx for synchronizing your question! Happy New Year!
    – Dr. belisarius
    Commented Dec 29, 2010 at 20:16
  • 1
    @blunders Here is a trick if you have a lot of columns as a result from the join en.allexperts.com/q/Excel-1059/…
    – Dr. belisarius
    Commented Dec 29, 2010 at 22:05
  • +1 Cool, thanks. One thing I might add is that it appears your VLOOKUP function needs "false" added as the last parameter; this will force exact matches only. Also, for the multi-column reuse, it appears that if you wrap the column that's being output like this "COLUMN(A1)" in the third parameter, that it will convert the column name to a numeral for VLOOKUP to use; which is only of use if the columns being matched are in the order you need and share the same lookup key; so, for your code example, it would work.
    – blunders
    Commented Dec 30, 2010 at 15:12
1

I needed this yesterday, and vlookup solved my problem. Basically, you look for a join value from one of the tables inside the other one, and then use a column offset to get another column from the same row. This may become tedious when your tables have many columns, and does not support left join, but may be enough for your use case.

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.