lookup across multiple columns

[Guest]

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

 

[Morrigan]

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

 

[Domenic]

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!

 

[Guest]

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]

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?