Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding text in a range
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding text in a range
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 On Oct 1, 6:20*pm, Always need help asap :-) <Always need help asap wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding text in a range
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 wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding text in a range
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. "Pete_UK" wrote: 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 On Oct 1, 6:20 pm, Always need help asap :-) <Always need help asap wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding text in a range
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!! "Bernard Liengme" wrote: 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 wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding text in a range
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<""),)) -- Thanks, Shane Devenshire "Bernard Liengme" wrote: 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 wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding a value between a range | Excel Worksheet Functions | |||
Functions- Finding and counting specified text in cell range | Excel Worksheet Functions | |||
Finding a value associated with a range | Excel Worksheet Functions | |||
finding name within range | Excel Worksheet Functions | |||
finding if name within range | Excel Worksheet Functions |