Finding text in a range

[Always need help asap :-)]

Hello. I'm a fairly new user to Excel and need help with an IF statement.
I've tried using ISTEXT, but that only seems to work with single cells.

I have a spreadsheet several columns wide and in the first column, need to
put a value, based on which column the text is in (There's only 1 item per
line).

Example:
Col A B C D..... J H I ..... W X Y Z
Results Text
Results Text

If the text is in Col. D, I need the result to be "IBM", if it's in Col. H,
then "CA", etc.
Can someone help me with this, please?

 

[Pete_UK]

What determines that it should be "IBM" if the text is in column D, or
"CA" for column H? Are these column names which are in B1:Z1? Or, do
you have a table somewhere that lists the names for each column?

Pete

 

[Bernard Liengme]

Assuming there are no other entries in the cells B1 to Z1
=CHOOSE(MATCH(LOOKUP(2,1/(A1:Z1<>""),A1:Z1),A1:Z1),"in a","in b","in
c","IBM","in e","in f","in g", "CA")

LOOKUP find the last item, MATCH tell what column it is in, CHOOSE pick the
text
But for 26 columns this will be horrid.

On another sheet (I used Sheet4) in A1:A26 enter the required text. So A4
has IBM and A* has CA
Then use
=INDEX(Sheet4!A1:A26,MATCH(LOOKUP(2,1/(A1:Z1<>""),A1:Z1),A1:Z1))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Always need help asap " <Always need help asap
@discussions.microsoft.com> wrote in message
news:[email protected]...

 

[Always need help asap :-)]

I've sorted the data, into columns, depending on manufacturer, e.g., all the
text in Columns A - G are IBM, H - L are CA, M - S are another manufacturer
and so on. The spreadsheets are rather wide, so rather than having to go
back and forth, I thought there'd be an IF statement that could help out.

 

[Always need help asap :-)]

Thank you, Bernard, but my spreadsheets are wider than 26 columns and the
text varies because each of the manufacturers makes different products. For
instance, both IBM and CA are manufacturers of TCP/IP, but if the text is in
the first few columns, then the manufacturer is IBM; in any of another range
of columns, it's CA.

Appreciate the help, though and will keep your solutions in mind - there's
no telling when I'll be able to use them!!

 

[ShaneDevenshire]

Hi,

Since you have only one item per line the following array formula will work:

=INDEX(B$1:N$1,,MATCH(1,N(B2:N2<>""),))

This formula is in A2 and assumes that the text entries you want to display
are in the range B1:N1. This formula is array entered (Press
Shift+Ctrl+Enter instead of Enter).

Now it the text you want to return is the text in the cell that has an entry
then:

=INDEX(B$2:N$2,,MATCH(1,N(B2:N2<>""),))