+ Reply to Thread
Results 1 to 5 of 5

lookup across multiple columns

  1. #1
    NHP
    Guest

    lookup across multiple columns

    I two worksheets in an Excel workbook. In Worksheet A, column A contains the
    company name, while columns B through S contain various ticker symbols of
    securities related to the company. The data are lined up such that the
    tickers and reference company are in the same row. In a separate Worksheet
    B, I have listed in a single column all of the ticker symbols. I would like
    to be able to search Worksheet A for the ticker symbol and have it return the
    company name that corresponds to the ticker. Is it possible to search through
    several columns for a single symbol, and then have Excel give me a
    corresponding name?

    The two worksheets look like this:

    Worksheet A

    Company Ticker1 Ticker2 Ticker3
    Exxon 145 XOM CDSX
    Citigroup C D453 CRFD
    Alcoa A REFA D4CF

    Worksheet B

    Ticker Company
    145 ?
    C ?
    A
    XOM
    D453
    REFA
    D4CF
    CRFD
    CDSX

  2. #2
    Forum Contributor
    Join Date
    03-12-2004
    Posts
    329
    Using your example, on sheet B, B2 = INDEX(A!$A$1:$A$4,SUMPRODUCT(--(A!$B$2:$D$4=B!A2)*ROW(A!$B$2:$D$4))). Copy across and down


    Quote Originally Posted by NHP
    I two worksheets in an Excel workbook. In Worksheet A, column A contains the
    company name, while columns B through S contain various ticker symbols of
    securities related to the company. The data are lined up such that the
    tickers and reference company are in the same row. In a separate Worksheet
    B, I have listed in a single column all of the ticker symbols. I would like
    to be able to search Worksheet A for the ticker symbol and have it return the
    company name that corresponds to the ticker. Is it possible to search through
    several columns for a single symbol, and then have Excel give me a
    corresponding name?

    The two worksheets look like this:

    Worksheet A

    Company Ticker1 Ticker2 Ticker3
    Exxon 145 XOM CDSX
    Citigroup C D453 CRFD
    Alcoa A REFA D4CF

    Worksheet B

    Ticker Company
    145 ?
    C ?
    A
    XOM
    D453
    REFA
    D4CF
    CRFD
    CDSX

  3. #3
    Domenic
    Guest

    Re: lookup across multiple columns

    Assumptions:

    Sheet1!A2:S4 contains the source data

    Sheet2!A2:A10 contains the ticker symbols

    Formula, confirmed with CONTROL+SHIFT+ENTER, not just ENTER:

    Sheet2!B2, copied down:

    =INDEX(Sheet1!$A$2:$A$4,MATCH(TRUE,MMULT(--(Sheet1!$B$2:$S$4=Sheet2!A2),T
    RANSPOSE(COLUMN(Sheet1!$B$2:$S$4)^0))>0,0))

    Hope this helps!

    In article <5DC72FC8-22AC-42D9-B4FE-7E2D46477BAF@microsoft.com>,
    NHP <NHP@discussions.microsoft.com> wrote:

    > I two worksheets in an Excel workbook. In Worksheet A, column A contains the
    > company name, while columns B through S contain various ticker symbols of
    > securities related to the company. The data are lined up such that the
    > tickers and reference company are in the same row. In a separate Worksheet
    > B, I have listed in a single column all of the ticker symbols. I would like
    > to be able to search Worksheet A for the ticker symbol and have it return the
    > company name that corresponds to the ticker. Is it possible to search through
    > several columns for a single symbol, and then have Excel give me a
    > corresponding name?
    >
    > The two worksheets look like this:
    >
    > Worksheet A
    >
    > Company Ticker1 Ticker2 Ticker3
    > Exxon 145 XOM CDSX
    > Citigroup C D453 CRFD
    > Alcoa A REFA D4CF
    >
    > Worksheet B
    >
    > Ticker Company
    > 145 ?
    > C ?
    > A
    > XOM
    > D453
    > REFA
    > D4CF
    > CRFD
    > CDSX


  4. #4
    NHP
    Guest

    Re: lookup across multiple columns

    I tried both of your forumulas and was unsuccessful. I receive the #VALUE
    sign in the cell. Does it matter the type of data that is in each cell? For
    instance, some values are text and other are numerical.

    "Domenic" wrote:

    > Assumptions:
    >
    > Sheet1!A2:S4 contains the source data
    >
    > Sheet2!A2:A10 contains the ticker symbols
    >
    > Formula, confirmed with CONTROL+SHIFT+ENTER, not just ENTER:
    >
    > Sheet2!B2, copied down:
    >
    > =INDEX(Sheet1!$A$2:$A$4,MATCH(TRUE,MMULT(--(Sheet1!$B$2:$S$4=Sheet2!A2),T
    > RANSPOSE(COLUMN(Sheet1!$B$2:$S$4)^0))>0,0))
    >
    > Hope this helps!
    >
    > In article <5DC72FC8-22AC-42D9-B4FE-7E2D46477BAF@microsoft.com>,
    > NHP <NHP@discussions.microsoft.com> wrote:
    >
    > > I two worksheets in an Excel workbook. In Worksheet A, column A contains the
    > > company name, while columns B through S contain various ticker symbols of
    > > securities related to the company. The data are lined up such that the
    > > tickers and reference company are in the same row. In a separate Worksheet
    > > B, I have listed in a single column all of the ticker symbols. I would like
    > > to be able to search Worksheet A for the ticker symbol and have it return the
    > > company name that corresponds to the ticker. Is it possible to search through
    > > several columns for a single symbol, and then have Excel give me a
    > > corresponding name?
    > >
    > > The two worksheets look like this:
    > >
    > > Worksheet A
    > >
    > > Company Ticker1 Ticker2 Ticker3
    > > Exxon 145 XOM CDSX
    > > Citigroup C D453 CRFD
    > > Alcoa A REFA D4CF
    > >
    > > Worksheet B
    > >
    > > Ticker Company
    > > 145 ?
    > > C ?
    > > A
    > > XOM
    > > D453
    > > REFA
    > > D4CF
    > > CRFD
    > > CDSX

    >


  5. #5
    Domenic
    Guest

    Re: lookup across multiple columns

    You need to confirm the formula with CONTROL+SHIFT+ENTER, not just
    ENTER. In other words, after you type the formula, press the CONTROL
    and SHIFT keys down, then while both keys are pressed down, press the
    ENTER key. Excel will place braces {} around the formula, indicating
    that you've entered the formula correctly.

    Does this help?

    In article <75290683-488A-45A3-8907-7D97630B32F9@microsoft.com>,
    NHP <NHP@discussions.microsoft.com> wrote:

    > I tried both of your forumulas and was unsuccessful. I receive the #VALUE
    > sign in the cell. Does it matter the type of data that is in each cell? For
    > instance, some values are text and other are numerical.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1