is such a reference, lookup function possible?

s2mcpaul

New Member
Joined
Oct 13, 2006
Messages
11
please help I have been trying to do this for some time and I am lost.

I have a worksheet with a list of 500+ companies and their symbols.

I have another worksheet with 7000+ companies that have b/w 10-20 columns of data. The companies in the 500 list are all in the 7000 list, but I need to narrow down/filter out the data that I do not need from the 7000 company list.

Basically I want to use either the company name or symbol from the 500 company list as a reference to retrieve the data from the 7000+ company list, how can I do this??

(The symbol from above will be best b/c there is no variation in spelling or issue -case sensitive)

Please help me I am lost and this would be of great help to me.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
You probably want to use Vlookup().

Review Excel help files on Vlookup and post back if you still need help.
 

s2mcpaul

New Member
Joined
Oct 13, 2006
Messages
11
I have and it makes limited sense. Can i use that function to return the entire row?

Here is an example;

in the 500 list I have:(sheet 1)
A B C D E
1 Company Name Ticker Symbol
2 3M Company MMM
3 Abbott Labs ABT

in the 7000 list I have:(sheet 2)
A B C D E
1 Company Name Ticker Symbol Industry Name SIC Exchange
2 @Road Inc ARDI Telecom. Services 4810 NDQ
3 1-800 Contacts CTAC Medical Supplies 8060 NDQ
4 1-800-ATTORNEY ATTY Publishing 2700 NDQ
5 3M Company MMM Chemical 2813 NYS


How do I get C:E for just 3M (or MMM) and then for the rest in my list which woould be Abbott labs without writing a new function for each of the 500 companies?

(let me clarify the term list it is not a list created via forms it is just a normal excel spreadsheet of companies)
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try

=Vlookup(B2,Sheet2!$B$2:$E$7000,Column(B$1),0)

Copied to the right to column E and then copied down as far as necessary.

This will lookup Ticker Name from Sheet 1 in the table from Sheet2 and return info in column C, D, E.
 

s2mcpaul

New Member
Joined
Oct 13, 2006
Messages
11

ADVERTISEMENT

I did this as a sample and not for the whole thing. This is what i have in my spreadsheet, but it only gives me MMM and not the entire row of data. What am I doing wrong.

=VLOOKUP(A4,Sheet2!$A$4:$E$22,COLUMN(Sheet2!A4:F22),0)

Also this gives the same just MMM

=VLOOKUP(A4,Sheet2!$A$4:$E$22,COLUMN(A4),0)
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
If you want to use the Company Name as a lookup value, then:

=VLOOKUP(A4,Sheet2!$A$4:$E$22,COLUMN(C$1),0) copied over and down.

This part, COLUMN(C$1), returns a column number which I can use as a column index number in order to extract info from the correct column in the table.

Initially, you said that using the symbol would be better so that is why my lookup value is in column B.

(The symbol from above will be best b/c there is no variation in spelling or issue -case sensitive)

If you still want to use the Symbol, then use:

=VLOOKUP(B4,Sheet2!$B$4:$E$22,COLUMN(B$1),0) copied across and down.
 

s2mcpaul

New Member
Joined
Oct 13, 2006
Messages
11
:>

Thanks a lot for your help it works!!!

Here is how I did it, for the record

1)
=VLOOKUP(Sheet3!B2,Sheet4!$B$2:$BW$7113,COLUMN(A2),0)
For the upper left cell

2)
=VLOOKUP(Sheet3!B3,Sheet4!$B$2:$BW$7113,COLUMN(A3),0)
drag down as far as needed

3)
=VLOOKUP(Sheet3!$B2,Sheet4!$B$2:$BW$7113,COLUMN(B2),0)
for the 1st cell to the right and drag as far right as need be

4)
then from the last step keep it selected and either double click or drag down to bottom and it will fill in the empty cells.
 

Forum statistics

Threads
1,136,991
Messages
5,679,004
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top