One of the most common tasks in Excel is checking whether a cell contains a value of interest. What kind of value can that be? Just any text or number, specific text, or any value at all (not empty cell). Continue reading
Comments on: Excel: If cell contains formula examples
by Svetlana Cheusheva, updated on
Comments page 3. Total comments: 198
Hello,
I have numbers on Column A1 that I need B1 to return with a name if the number matches
For example
A1 is 118 and B1 needs to be Chad
A1 is 132 and B1 needs to be Mike
A1 is 109 and B1 needs to be Tuan
A1 is 110 and B1 needs to be Kevin
A1 is 115 and B1 needs to be Carlos
A1 is 105 and B1 needs to be Mark
A1 is 107 and B1 needs to be Curtis
and so on, I have been fighting this all afternoon.
Use VLOOKUP formula
Is the following possible and if so what formula would I use to pull this off?
- Column A has rows of Summary data from problem tickets which will contain the problem ticket ID and other text.
- The problem ID will always be 15 characters in length
- The format of the Problem ID is USPM followed by the number for example USPM12345678911
Is there a formula that will look at for example cell A2 for *USPM* and return everything within the * * IN CELL b2? For example A2= USPM12345678911 the formula looks at A2 to see if it contains USPM and if it does it returns USPM and the next 11 charters to its right.
=MID(A1,FIND("USPM",A1),19)
Robert - You are my hero!!! Thanks so much. This worked like a charm :)
HI,
I Have a column, M, named "Qualifications". It contains different strings of Academic qualification data. But I just need to pick the specific qualification. E.g If the string reads " Masters of Education", I just need "Masters" If it reads "Certificate of Secondary Education", I need KCSE, If it reads "Bachelors degree in Medicine", I just need "Bachelors".
Tried using the formula below but didn't work. PLease HELP
=LOOKUP(M2{"*Doctor*";"*Master*";"*Bachelor*";"*Diploma*";"*Secondary*";"*CSE*";"*EACE*";"*Primary*";"*CPE*";"*N/A*"} "PhD";"Masters";"Bachelors";"Diploma";"KCSE";"KCSE";"KCSE";"KCPE";"KCPE";"N/A"})
Teddy:
Wildcards can be used in some functions, but not in others. If you need to use the * in the formula you'll need to use VLOOKUP or an INDEX/MATCH formula.
Here's how to write a nested IF statement for the samples you provided:
=IF(A74="Doctor","PHD", IF(A74="Master","Masters",IF(A74="Bachelor","Bachelors", IF(A74="Secondary","KCSE", IF(A74="Diploma","KCSE", IF(A74="CSE","KCSE", IF(A74="EACE","KCSE", IF(A74="Primary","CPE", IF(A74="N/A","N/A")))))))))
You can use this as the basis for a huge IF/OR statement, but it would get crazy long.
Read the VLOOKUP or INDEX/MATCH articles here on AbleBits and see if that helps.
Hi,
I have two data tables with multiple row and column data. in table 1, i have alphanumeric code and dates while in table 2 i have similar alphanumeric code. i wanted to search the table 2 for any part of the alphanumeric code from table 1 and on locating the same, fetching me the date againt the said code.
Rajat:
Do you have sample data from each table you can post here? It's easier to try and help if I can see what you're working with.