VLOOKUP

Alexander · Apr 16th, 2002, 11:05 AM

If I have a value in one file (A1) that needs to match to a value in another file which is in two columns (A1:B1), how do I get a return in column C. Example:
File 1
A
Cat
Dog
Pig
Horse

File 2
A B
Cat Meow
Frog Croak
Dog Woof
Chick Peep
Horse Neigh
Duck Quack
Pig Oink

File 1 should now be:
A B
Cat Meow
Dog Woof
Horse Neigh
Pig Oink

[ This Message was edited by: Alexander on 2002-04-16 10:15 ]

Dave Patton · Apr 16th, 2002, 11:11 AM

What problem are you having with Vlookup?
What result do you get?

Could you show your formula?

If the information is not sorted and you want an exact match, add the 4th parameter
- False.

See Help for Syntax.

Alexander · Apr 16th, 2002, 11:18 AM

Is there a way I can send you a copy of the files so that you may look at it and tell me how to write the formula? My email is bettillee@msn.com.

Brian from Maui · Apr 16th, 2002, 11:31 AM

Quote:

On 2002-04-16 10:05, Alexander wrote:
If I have a value in one file (A1) that needs to match to a value in another file which is in two columns (A1:B1), how do I get a return in column C. Example:
File 1
A
Cat
Dog
Pig
Horse

File 2
A B
Cat Meow
Frog Croak
Dog Woof
Chick Peep
Horse Neigh
Duck Quack
Pig Oink

File 1 should now be:
A B
Cat Meow
Dog Woof
Horse Neigh
Pig Oink

[ This Message was edited by: Alexander on 2002-04-16 10:15 ]

Assuming file 1 and 2 are worksheets

In B1 of file 1 enter
=VLOOKUP(A1,file2!$A$1:$B$7,2,1)

Anne Troy · Apr 16th, 2002, 11:36 AM

Hi there. Brian's explanation looks like it will work for you. If not, or if you're having difficulty understanding the vlookup formula, you can check this out:

http://www.thewordexpert.com/excel.h...DreadedVlookup

Hope it helps!

Dave Patton · Apr 16th, 2002, 11:44 AM

or try Brian's formula
revised slightly

In B1 of file 1 enter
=VLOOKUP(A1,file2!$A$1:$B$7,2,0)

Alexander · Apr 16th, 2002, 12:00 PM

This works fine, except file 1 has 2000 lines and file 2 will vary, from 100 to 1000. It is not matching correctly.

Alexander · Apr 16th, 2002, 12:12 PM

Never mind. I was wrong. It works great and thank you all!

Brian from Maui · Apr 16th, 2002, 12:17 PM

Quote:

On 2002-04-16 11:00, Alexander wrote:
This works fine, except file 1 has 2000 lines and file 2 will vary, from 100 to 1000. It is not matching correctly.

If column A in file 2 has data that is repeated, it will match the first data that is repeated. Not sure what you mean by file 2 will vary, but you can name the range in file 2 i.e =vlookup(a1,range name,2,0)

Apr 16th, 2002, 11:36 AM	#5
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,512	Hi there. Brian's explanation looks like it will work for you. If not, or if you're having difficulty understanding the vlookup formula, you can check this out: http://www.thewordexpert.com/excel.h...DreadedVlookup Hope it helps! __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 16th, 2002, 11:05 AM	#1
Alexander New Member Join Date: Apr 2002 Posts: 45	If I have a value in one file (A1) that needs to match to a value in another file which is in two columns (A1:B1), how do I get a return in column C. Example: File 1 A Cat Dog Pig Horse File 2 A B Cat Meow Frog Croak Dog Woof Chick Peep Horse Neigh Duck Quack Pig Oink File 1 should now be: A B Cat Meow Dog Woof Horse Neigh Pig Oink [ This Message was edited by: Alexander on 2002-04-16 10:15 ]

Apr 16th, 2002, 11:11 AM	#2
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	What problem are you having with Vlookup? What result do you get? Could you show your formula? If the information is not sorted and you want an exact match, add the 4th parameter - False. See Help for Syntax.

Apr 16th, 2002, 11:18 AM	#3
Alexander New Member Join Date: Apr 2002 Posts: 45	Is there a way I can send you a copy of the files so that you may look at it and tell me how to write the formula? My email is bettillee@msn.com.

Apr 16th, 2002, 11:44 AM	#6
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	or try Brian's formula revised slightly In B1 of file 1 enter =VLOOKUP(A1,file2!$A$1:$B$7,2,0)

Apr 16th, 2002, 12:00 PM	#7
Alexander New Member Join Date: Apr 2002 Posts: 45	This works fine, except file 1 has 2000 lines and file 2 will vary, from 100 to 1000. It is not matching correctly.

Apr 16th, 2002, 12:12 PM	#8
Alexander New Member Join Date: Apr 2002 Posts: 45	Never mind. I was wrong. It works great and thank you all!