super global search and place

Posted by April Yergin on February 11, 2002 3:34 PM

Thanks to anyone that can help me. --April

Example of what I'd like to do:

Source file
A B
1 Jack
2 Mary
3 Peter
4 Jane
5 Sam

Target file
A (To be filled in by clever programming)
3 Peter
4 Jane
2 Mary
4 Jane
1 Jack
1 Jack

etcetera

Posted by Mark W. on February 11, 2002 4:24 PM

No clever programming required...

Enter =VLOOKUP(Sheet2!A1,Sheet1!A1:B5,2,0) into
cell Sheet2!B1 where Sheet1 is your Source and
Sheet2 is your Target.

Posted by Mark W. on February 11, 2002 4:27 PM

Minor revision... plus additional instructions...

Enter =VLOOKUP(Sheet2!A1,Sheet1!$A$1:$B$5,2,0) into
cell Sheet2!B1... and copy down to Sheet2!B5.

Posted by April Yergin on February 12, 2002 8:28 AM

Re: Minor revision... plus additional instructions...

Thanks very much! Mark

Posted by April Yergin on February 12, 2002 8:41 AM

Almost worked, Mark

Almost works but not quite. The problem is that I have multipule rows in the target column that refer to only one row in the source.

Source data:

1 American Motors
2 Jeep
3 AM General
6 Chrysler
7 Dodge
etc...

Target data:

1
1
1
2
2
3
3
3
3
6
6
etc

Here's what I get when I use your program:

1 American Motors
1 #N/A
1 #N/A
2 #N/A
etc...

Posted by Mark W. on February 12, 2002 8:50 AM

Shouldn't make any difference...

Are you sure that you're using...

=VLOOKUP(Sheet2!A1,Sheet1!$A$1:$B$5,2,0)

...where Sheet2 is your Target and Sheet1 is
your Source?

I get...

{1,"American Motors"
;1,"American Motors"
;1,"American Motors"
;2,"Jeep"
;2,"Jeep"
;3,"AM General"
;3,"AM General"
;3,"AM General"
;3,"AM General"
;6,"Chrysler"
;6,"Chrysler"}

Posted by April Yergin on February 12, 2002 11:12 AM

Works great, Mark.

Works great now. Thanks so much. :)

I entered: =VLOOKUP(Sheet2!A1,Sheet1!A1:B5,2,0) into cell Sheet2!B1 where Sheet1 is Source and Sheet2 is Target.

I see in your latest email a different code. It looks like you put in some dollar signs. Perhaps that is why. Thanks again. Are you sure that you're using... =VLOOKUP(Sheet2!A1,Sheet1!$A$1:$B$5,2,0) ...where Sheet2 is your Target and Sheet1 is }