In this lesson, We are going to explore:
0:00 Getting started.
0:03 INDEX MATCH Asterisk Wildcard Exercise.
0:18 What are wildcards?
2:51 INDEX MATCH Question Mark Wildcard Exercise.
All explanations are simple and to the point.
Feel free to make your own copy of this spreadsheet:
https://docs.google.com/spreadsheets/...
If you found this video helpful, give us an old like and share it with some people you think this could be helpful for. And if you're not already a subscriber, make sure to hit the subscribe button and also the notification bell to be notified every time a video is uploaded
Useful Link:
Ben Collins (Google Developer Expert & Data Analytics Instructor)
https://www.benlcollins.com/
Google Sheets training:
https://support.google.com/a/users/an...
The whole playlist link:
• The most useful formulas - Google Sheets |...
Follow us on Pinterest:
https://www.pinterest.it/GoogleWorksp...
Sound from Zapsplat.com
Apple logo
https://iconscout.com/contributors/pi...
Windows logo
https://pixabay.com/it/users/
Welcome back, Educators.
Our mission is to extract any cell that has the word ice anywhere it is found.
To achieve that, we use wildcards.
What are wildcards?
They are symbols used to represent one or more characters.
The Asterisk (*)matches any number of characters.
The question mark (?) matches a single character.
To start, we use MATCH to return the position of the cells that have the word ice.
The search_key of MATCH is an array of the word ice with an asterisk wildcard.
It returns the position of the cell that starts with the word ice.
And which has this word in the middle.
And also at the end.
The range is the open-ended column B.
we set the search_type to 0 to return an exact match.
Finally, we surround the formula with the ArrayFormula function.
It returns the rows numbers that have the word ice.
We’ve added a drop-down menu to this cell.
That returns all the values in the open-ended column A.
And we’ve added a custom number format to facilitate reading the data.
Now, we use the INDEX to return the value of each cell.
The reference is the open-ended column B.
We change the row number dynamically by noosing an item from the drop-down menu.
When we select Start with, that returns the cell that has the word ice in the beginning.
By changing the drop-down menu item, it returns the cell that is associated with it.
Our mission is to extract the cells that have the word Organization.
No matter if it is written with Z or S.
And here is come the power of the wildcard (?).
We could use the same formula that we’ve created before. Just replace its array with this one.
That’s it, have fun. Thanks for watching.
If you like the video, please give us a thumbs up.
And subscribe to our channel.
Поделиться в:
Доступные форматы для скачивания:
Скачать видео mp4
Информация по загрузке:
Скачать аудио mp3
Похожие видео
array(10) {
[0]=>
object(stdClass)#5376 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "uuQhewu4IpY"
["related_video_title"]=>
string(79) "MATCH COUNTIF - Return all Matches - Tilde Wildcard | Google Sheets Formulas 26"
["posted_time"]=>
string(21) "3 года назад"
["channelName"]=>
string(25) "Google Workspace Tutorial"
}
[1]=>
object(stdClass)#5349 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "b9WoDkPOTPs"
["related_video_title"]=>
string(61) "Excel IF Function with PARTIAL Text Match (IF with Wildcards)"
["posted_time"]=>
string(19) "6 лет назад"
["channelName"]=>
string(13) "Leila Gharani"
}
[2]=>
object(stdClass)#5374 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "1UTddawGKTI"
["related_video_title"]=>
string(51) "Class 04 Date 14 10 2024 Text Replace ,Table Create"
["posted_time"]=>
string(24) "39 минут назад"
["channelName"]=>
string(15) "Shikder Tech Bd"
}
[3]=>
object(stdClass)#5381 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "7N1j1SAc_sc"
["related_video_title"]=>
string(47) "How to Use Wildcard Characters in Google Sheets"
["posted_time"]=>
string(21) "3 года назад"
["channelName"]=>
string(17) "Modern School Bus"
}
[4]=>
object(stdClass)#5360 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "98dnBA5WYLY"
["related_video_title"]=>
string(58) "15 Spreadsheet Formulas Working Professionals Should Know!"
["posted_time"]=>
string(21) "3 года назад"
["channelName"]=>
string(7) "Jeff Su"
}
[5]=>
object(stdClass)#5378 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "n0vqkuRyMvQ"
["related_video_title"]=>
string(67) "Data Analyst Explains When to Use VLOOKUP vs XLOOKUP vs INDEX MATCH"
["posted_time"]=>
string(21) "2 года назад"
["channelName"]=>
string(7) "Mo Chen"
}
[6]=>
object(stdClass)#5373 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "g_d_1DPktzo"
["related_video_title"]=>
string(72) "Шоу саксофона и глубокого баса от Black Cats"
["posted_time"]=>
string(25) "2 месяца назад"
["channelName"]=>
string(14) "Noir Jazz Cats"
}
[7]=>
object(stdClass)#5383 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "nec3aZM8aUY"
["related_video_title"]=>
string(83) "Deep House Mix 2024 | Deep House, Vocal House, Nu Disco, Chillout Mix by Diamond #3"
["posted_time"]=>
string(19) "1 год назад"
["channelName"]=>
string(7) "Diamond"
}
[8]=>
object(stdClass)#5359 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "RnHC1XiNWS8"
["related_video_title"]=>
string(94) "Венедиктов – страх, Симоньян, компромиссы / вДудь"
["posted_time"]=>
string(21) "6 дней назад"
["channelName"]=>
string(10) "вДудь"
}
[9]=>
object(stdClass)#5377 (5) {
["video_id"]=>
int(9999999)
["related_video_id"]=>
string(11) "jM14cBDqQXA"
["related_video_title"]=>
string(98) "Россия обстреляла Boeing / Массовая эвакуация из столицы"
["posted_time"]=>
string(23) "7 часов назад"
["channelName"]=>
string(10) "NEXTA Live"
}
}
