Sincerely, the method of leila is better than your method...!! with your formula, you can not search for a number in a series of digits, unless the cell contains a combination of numbers and text
Last day someone ask me something like this, I gave her a way almost same as your solution but a little easier for two criteria. It will be smaller formula. =IF(OR(ISNUMBER(SEARCH({"DE","AT"},A5))),"Europe","") Thanks for your training. Mahmoud Baniasadi , from Iran
How do I carry over the exceeding value or the remaining one? For example I have to pay $10,000 but my salary is only $2,100 January is 0 all used and going vacation on february. February is 0 because of vacation and will only receive after I come back (weird company policy) March is 2,100 + 2,100 (paid leave from previous month) + 2,600 (Paid ticket) - 500 (expenses) = $6,300 April 2,100 so on and so forth so 5% of 2100 is 100 for savings. 25% of 2,000 is 500 for daily expenses. 1,500 is can Pay for remaning loan. if 10,000 - 6,300 = 3,700. How can I carry this remaining value to other cell. So that the next month which is 1,500 salary will be deducted.??? Hope you get my explaination.
I had to check 7,000 entries of comments (long'ish text) from customers, we were interested in finding a particular word in the comments. Your tutorial here just saved me days of boring 'eye-balling' work, Wonderful! Thank you. BTW, I prefer your style of teaching than many others - it's always clear, no rambling and intuitive. Many thanks again.
Beautifully explained Long since I was wondering why the * function doesnt work to retrieve a particular letter while using IF Awesome piece of information
I appreciate that you take us through the steps of things that won't work and explain why. This makes it easy to follow and is useful for cases where those functions that don't work in this case might be useful for something else we encounter in the future. Thank you for your work on this!
nice video leila. very inspiring video thank you. my humble solution would be this =IF(SUBSTITUTE(A5,"AT","")=A5,"","AT"). in french version of excel ► =SI(SUBSTITUE(A5;"AT";"")=A5;"";"AT") . i used IF but without *. thank you .
You are great at making complicated things simple. Thanks for this video . It is exactly what I'm looking for except I need it in Google Sheets. What tweaks should I make for it to work in Google Sheets?
Hi Leila, your lessons really helpful for me always since i was limited knowledge with excel and but now having more and more knowledge with spreadsheet sheets that i can do my job more easily than previous. if you have any web or page please share that. Thank you very much!!! greeting from Sri lanka
Hi, let me just thank first for how helpful your videos have been for quiet some time now, I have recently came across a complex problem which takes a lot of time to do, If I could contact you and share this maybe you can take it on, it'll be a great help to others too. Hope we talk soon.
Hi I am a regular follower of your VDOs and learnt a lot from them. I want to ask a question that How to extract Alphanumeric if and only if i put numbers . For example I Xlookup a cell with a value of ABC1234 and if I type only 1234 the result should search and give me ABC1234? Is there any way to do such? Please advice.
Thanks for the wonderful video. A quick question, how to multiple search results using this formula? For example, if "AT" or "DE", it should be Europe, if "US" return value should be "USA", if "IN" return value should be "India"? Please share a like if you have a video for this? Thank you as always.
Hey Leila. I've used the isnumber function for this too but I think you can also do this with the If function along with a match since the match allows for the asterisks wildcards. Then if the match works you can use the if conditionals.
Also - Even though Conditional Formatting will NOT allow wildcards, lambda .... - as it turns out you can use name manager to define a function, or conceal a lambda function. Then Conditional Formatting WILL allow all of these things.
Hi, how can I use this same formula for multiple returns, such as you mentioned AT or DE then "Europe", but in my case I need to get, if AT then "Europe" or DE then "USA"
I have to setup a bonus worksheet. If gross profit per deal is less than or equal to $1000 then it's 2% of total gross, $1001-1099 is 3% and if it's greater than or equal $1100 then 5%. How do I do that on excel? Thank you.
Hi Lelia, I checked most of your videos and couldn't find how to highlight cells with specific text in excel. In my case, I have a pool of 13 words and I want to write just one rule that consists of all the 13 words so that if the cells have any of those 13 words is highlighted. I'm sharing a sample of what I wrote for two words (but it is not working) =or($A$2:$D$896=“service”, $A$2:$D$896=“quality”)
What if I have a list of words (like AT and DE but more, maybe 10) and I want to search a column and every time it finds one of those words in the list it will return that word (like AT or DE) Also your videos are great, very clear. Thank you :)
Thanks a lot, I have a none Excel question: You are showing your face in many portions of this video, you didn't do this before, is it a new RU-vid constraint in order to get channel monetized?
Is it possible to do this with a range/array of strings, As in, instead of 2 (De or At), could we have excel search through hundreds of cells in a column, and return a value if isnumber returns true?
Hello. Is there any formula on how to match data as in the following example. First sheet: In one column are data (A, B, C...), in another column are data (1, 2, 3...). Those colums should be related: A=1, B=2, C=3...). Second sheet: There is third column, and I want data to be shown automaticaly, when I chose "A" (from a drop down list using Data Validation) in fourth column as "1".
I have an excel they I’ve done trims too; With personal information of people. I noticed at the end that some of the phone numbers don’t match up (most likely an error when I copied them over from the raw/original excels). I opened a new excel with the edited version and original side by side. Basically I would like to ask what the most efficient way to copy the accompanying phone numbers from the raw data to any exact match names in the edited version so that it mass swaps the numbers from raw to new for only names with exact matches. Thank you.
Hi Leila! Thanks for the video. Was wondering if you can do a text match with a range of cells instead of having to reference each one individually (lets say you have 20 words instead of just DE and AT). Is there a formula for that? Thanks in advance.
I was struggling with this myself. Try the following =IF(SUMPRODUCT(--ISNUMBER(SEARCH($A$1:A$20,B1)))>0,"YES","NO"), where $A$1:A$20 is the range with the words and B1 is the cell you are checking against. If there is a partial match, then the formula should return YES.
Thanks for your tips. How about an array of values instead a single cell? I mean, sometimes we need to find the nth position of such value (criteria) and we we use the array formula SMALL(IF(RANGE=CRITERIA,ROW(RANGE)-MIN(ROW(RANGE))+1,NTH_POSITION) . How to make wild card work in this particular use of IF formula once is a range to search the value instead a single cell???
@@LeilaGharani There are situations that require to get the Nth position of a value based a list of repeatable values. Unfortunately the MATCH function only provides the first position. The formula I wrote uses the IF function, but when I use the wild card it doesn't work. I hope I explained it. Thanks anyway
Hi, great teacher, I need to know how to get different return, like, if AT get Europe, or if DE get France ? Get different returns with different values.
What if same example but with numbers only?! I have a list of random numbers in a column and i want pick for example 4 different matching-criteria numbers (with starting with *) and placed them in another sheet onto 4 rows ? hope it is clear
i was looking over 4 weeks for this formula in the internet and suddenly youtuve recomends me this video and you gave me the solution i finnaly needed thank you so much
If I have a table of company codes, and on another cell, I want a drop down, with data validation, only the codes with the letters "IL" in them. Do you have a video for that kind of thing? I normally use INDIRECT to make my drop down list from a table, but I want it to list only cells with "IL" in them. Not sure how else to describe it on here, wish I could post a picture to show what I mean lol
Hi Leila! Needing some help :) I’m trying to get a cell in cell C2 with text to change purple if the word “dog” is in cell A2 and going all the way down the entire spreadsheet. The word “dog” may be imbedded within another word such as frenchbulldog set up as one word or bulldogpuppy again one word but “dog is now in the middle or even bulldog is this possible? Awesome videos really learning a lot of your channel!
If I have a single cell that contains a lot of info that is squished together (no spaces between the words or commas), such as "coat,hat,shoes" ....what is the easiest formula I can use to get a return yes result, if the cell contains the word "hat"? Nothing I try seems to work and I don't want to have to reformat the entire sheet that my friend made 😭
The suggestion that Alpha Slate made in his comment should get you the desired result. Here it is modified for your request. Tested it myself and it works! =IF(COUNTIF(A5,"*hat*")>0,"yes","")
Hi Laila, how are you, for wildcard if formula like you use separately alfa to pick from other cell but i dont mention separately alfa can i pick from other cell.
