is such a reference, lookup function possible?

You probably want to use Vlookup().

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

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 havesheet 1)
A B C D E
1 Company Name Ticker Symbol
2 3M Company MMM
3 Abbott Labs ABT

in the 7000 list I havesheet 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)

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.

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)

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)

Click to expand...

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.

:>

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.