software

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


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 downNHP Wrote:
gt; I two worksheets in an Excel workbook. In Worksheet A, column A contains
gt; the
gt; company name, while columns B through S contain various ticker symbols
gt; of
gt; securities related to the company. The data are lined up such that the
gt; tickers and reference company are in the same row. In a separate
gt; Worksheet
gt; B, I have listed in a single column all of the ticker symbols. I would
gt; like
gt; to be able to search Worksheet A for the ticker symbol and have it
gt; return the
gt; company name that corresponds to the ticker. Is it possible to search
gt; through
gt; several columns for a single symbol, and then have Excel give me a
gt; corresponding name?
gt;
gt; The two worksheets look like this:
gt;
gt; Worksheet A
gt;
gt; Company Ticker1 Ticker2 Ticker3
gt; Exxon 145 XOM CDSX
gt; Citigroup C D453 CRFD
gt; Alcoa A REFA D4CF
gt;
gt; Worksheet B
gt;
gt; Ticker Company
gt; 145 ?
gt; C ?
gt; A
gt; XOM
gt; D453
gt; REFA
gt; D4CF
gt; CRFD
gt; CDSX--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: www.excelforum.com/member.php...foamp;userid=7094
View this thread: www.excelforum.com/showthread...hreadid=527305Assumptions:

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))gt;0,0))

Hope this helps!

In article gt;,
NHP gt; wrote:

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

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.

quot;Domenicquot; wrote:

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

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 gt;,
NHP gt; wrote:

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