Lookup based on 2 columns; return 1 value

[MsBeverlee]

I have 2 separate workbooks and I need a function that will lookup an
attendee name in workbook #2 and return the corresponding confirmation number
into workbook #1. Here is an example:

Workbook #1 contains the following 3 columns

Confirmation # First Name Last Name
John Smith
Julie White
David Anderson

Workbook #2 contains the confirmation # I need in column 3. How can I do a
lookup formula that will return the corresponding confirmation #?

Thanks for your help.

 

[Bernard Liengme]

My Sheet1 in Book2 looks like your: first name in B, last name in C
My Sheet2 in Book3 has first name in A, last in B and confirmation number in
C
On Sheet2 of Book3, I inserted a new column C and in C1 enter =A1&B1; I
copied this down the column
Back on Sheet1 of Book1, in cell A1 I entered the formula
=VLOOKUP(B1&C1,[Book3]Sheet1!$C$1:$D$3,2,FALSE)
copied it down the column
This gives me the confirmation numbers
If the second book has a space in its name we need something like
=VLOOKUP(B1&C1,'[Conf Numbers.xls]Sheet1'!$C$1:$D$3,2,FALSE)
best wishes

 

[MsBeverlee]

I tried it but it is not working. I am either getting a #REF error or it's
yielding the incorrect match. I'm not sure what I'm doing wrong. I even
took your suggestion of adding another column to combine the first & last
name (i.e. =B1&C1). What am I doing wrong? Here is what I have so far:

Workbook #1: Attendee List

Column
A B C

Row Confirmation # First Name Last Name
2 John Smith
3 Julie White
4 David Anderson


Workbook #2: Delegate List

Column
A B C
Row Confirmation # First Name Last Name
2 10 John Smith
3 12 Julie White
4 25 David Anderson

What I want to be able to do is lookup & match the first & last name of
Workbook #1 on Workbook #2 and return the corresponding Confirmation # onto
Workbook#1. For example, I want to lookup John Smith and if it matches on
Workbook #2, return the confirmation number 10. But it has to be exact
matches only.

What is the exact, step-by-step formula I would need to do this? Thanks!

My Sheet1 in Book2 looks like your: first name in B, last name in C
My Sheet2 in Book3 has first name in A, last in B and confirmation number in
C
On Sheet2 of Book3, I inserted a new column C and in C1 enter =A1&B1; I
copied this down the column
Back on Sheet1 of Book1, in cell A1 I entered the formula
=VLOOKUP(B1&C1,[Book3]Sheet1!$C$1:$D$3,2,FALSE)
copied it down the column
This gives me the confirmation numbers
If the second book has a space in its name we need something like
=VLOOKUP(B1&C1,'[Conf Numbers.xls]Sheet1'!$C$1:$D$3,2,FALSE)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

I have 2 separate workbooks and I need a function that will lookup an
attendee name in workbook #2 and return the corresponding confirmation
number
into workbook #1. Here is an example:

Workbook #1 contains the following 3 columns

Confirmation # First Name Last Name
John Smith
Julie White
David Anderson

Workbook #2 contains the confirmation # I need in column 3. How can I do
a
lookup formula that will return the corresponding confirmation #?

Thanks for your help.

 

[Dave Peterson]

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

 

[Bernard Liengme]

Make life easy fro yourself:
A) On the delegate list
1) In D1 enter =B1&C1 to concatenate the names; in E1 enter =A1
2) Copy these down the columns
B) On the Attendee List in A1 enter
=VLOOKUP(B1&C1,[Book3]Sheet1!$D$1:$E$3,2,FALSE
but change the file name (Book3) and Sheet name (Sheet1) to fit your
circumstances. Also change range $D$1:$E$3 to $D$1:$E$NNNN where NNNN is the
last row of your table
It will work, I promise. Just be slow and careful
Or send me the file (private email, not to newsgroup)
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

I tried it but it is not working. I am either getting a #REF error or it's
yielding the incorrect match. I'm not sure what I'm doing wrong. I even
took your suggestion of adding another column to combine the first & last
name (i.e. =B1&C1). What am I doing wrong? Here is what I have so far:

Workbook #1: Attendee List

Column
A B C

Row Confirmation # First Name Last Name
2 John
Smith
3 Julie
White
4 David
Anderson


Workbook #2: Delegate List

Column
A B C
Row Confirmation # First Name Last Name
2 10 John Smith
3 12 Julie White
4 25 David Anderson

What I want to be able to do is lookup & match the first & last name of
Workbook #1 on Workbook #2 and return the corresponding Confirmation #
onto
Workbook#1. For example, I want to lookup John Smith and if it matches on
Workbook #2, return the confirmation number 10. But it has to be exact
matches only.

What is the exact, step-by-step formula I would need to do this? Thanks!

My Sheet1 in Book2 looks like your: first name in B, last name in C
My Sheet2 in Book3 has first name in A, last in B and confirmation number
in
C
On Sheet2 of Book3, I inserted a new column C and in C1 enter =A1&B1; I
copied this down the column
Back on Sheet1 of Book1, in cell A1 I entered the formula
=VLOOKUP(B1&C1,[Book3]Sheet1!$C$1:$D$3,2,FALSE)
copied it down the column
This gives me the confirmation numbers
If the second book has a space in its name we need something like
=VLOOKUP(B1&C1,'[Conf Numbers.xls]Sheet1'!$C$1:$D$3,2,FALSE)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

I have 2 separate workbooks and I need a function that will lookup an
attendee name in workbook #2 and return the corresponding confirmation
number
into workbook #1. Here is an example:

Workbook #1 contains the following 3 columns

Confirmation # First Name Last Name
John Smith
Julie White
David Anderson

Workbook #2 contains the confirmation # I need in column 3. How can I
do
a
lookup formula that will return the corresponding confirmation #?

Thanks for your help.