Search Values in a Cell to Perform a Lookup on Specific Text
Вставка
- Опубліковано 12 вер 2024
- Have you done lookups before? Usually it's quite straight-forward...you have a value in a cell that you want to lookup up in another table but bring back an adjacent value that is aligned with it. The complication come when the cell you are looking up the value is full of other text and you just want to pull out or search for a define text and use that text as the looked up value. At first you may think that you'd need to have a helper column with a function to search out the right value and then another helper column to perform the lookup. Well this can all be done in one column with the LOOKUP and SEARCH function. It's almost like a wildcard search. I got this tip from Debra Dalgleish's contextures blog (check it out...she's got great Excel tips). See the video to learn how it's done.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to / doughexcel
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
For more videos that cover lookup concepts see the playlist at ua-cam.com/play/PL-n8f1cY_Qw95JHWjCjPofsCd7hCWjI8l.html
...
I know im asking the wrong place but does anyone know of a tool to get back into an Instagram account..?
I somehow lost my account password. I would appreciate any tricks you can offer me.
@Brian Ryder instablaster :)
@Maximo Lennon i really appreciate your reply. I found the site on google and Im trying it out now.
Takes quite some time so I will get back to you later when my account password hopefully is recovered.
@Maximo Lennon It did the trick and I now got access to my account again. Im so happy:D
Thank you so much, you really help me out !
This is beyond elegant..!! I have used complex index match lookups for the same output after literally hours of work. This is simply AWESOME..!! Thank you Doug..!!
Glad you liked!
I really thank for your this video, which actually helped me. Thanks Doug
Thank you! I've been looking up on formulas to use for our business and yours was the only one that worked. Thank you once again.
You’re welcome!
You saved me a thousand hours! God Bless You!!!
Glad I could help!
Very Nice , superb option, It has reduced my weeks of work to few minutes. Thanks a lot for this great video.
Thanks Mohammed Shamsuddin, glad it helped!
Very elegant formula. Did not understand the explanation but just know it works lol
Thanks Study, glad it helped!
Thanks a lot ..I was searching for this formula since last week.. finally I got it..this formula will save my lots of time...😀👍..
Glad it helped you!
I have been looking for a solution like this for so long, thank you!!
You are welcome!
You have no idea how you just saved my life
Thank you very much ❤️
You're welcome 😊
Thanks for the video. This was exactly what I needed. Much appreciated!
Thanks Ellwood Riesing, glad it helped!
Its absolute magic !! ❤
This is what ive been looking for... great help.. thanks 😊
Glad it was helpful!
This is awesome. Its exactly what I needed, Thank you!
You're very welcome!
You are a life-saver my good man!! Keep up the great work!!!
Hi Ben ベンジャミン Robinson, glad you liked it, thanks for commenting!
Excellent, very helpful. Thanks for sharing this.
You're very welcome!
Hey! Thank you so much. I was looking for it since long.
You’re welcome!
🙏🙏🙏👍👍👍🤩🤩🤩🤩🤩 Amazing tip and thanks to you and the original writer of this formula. I am literally using it now.
Wonderful!
Fantastic! Loved the easy to understand explanation :)
Thanks Jock Murray, glad it helped!
amazing ! it works !!
old video but still so useful, thanks a lot for making it!
Glad you thought so!
best formula on youtube for this issue ,,, thanks
Wow! Thanks!
Thank you soooooo much, this is exactly what I was looking for!!
You’re welcome!
Very Helpful. Thank you.
You’re welcome!
Brilliant! it works great
Great to hear!
5 GOLD STARS ....Saved me hours.
Thanks JODY ASHLEY, glad it helped!
Thank you! Really helps a lot...
Glad it helped!
You rock. Nicely explained as well.
Hi Khajoor, thanks for the kind words!
Thanks this worked perfectly!!!
Nice!
Thanks!
this was really helpful, thanks man!
Welcome!
Thank you so much for your kind help. Great video.
Hi Pankaj Verma...you're welcome, glad you liked!
Amazing!
Thank you!
Thank you too!
Simply awesome
Thanks a lot 😊
This is awesome! It works very well in my Excel. However, the formula somehow does not work in my Google Sheet. I have a title of the product and wanted to extract the size, color variation of the product. I have the set of the slandered color, size variation. Can you help me on this.
Brilliant! Truly elegant solution. Thanks for bringing it to our attention.
Hi nboisen, thanks for the comment!
is this also applicable on spreadsheet?
Thank you so much boss, please create some videos on piovt table extra ordinary.
Hi mahesh nagvekar, thanks for the comment! Various pivot table vid here ua-cam.com/video/5gmikbTmYUQ/v-deo.html
Thanx man !! It helped alot..
You're welcome!
Awesome
This was AWESOME..!! However this not working when search array or lookup vector has blank cells in between is there is any way to handle the situation.
Interesting, I've never used Lookup, but will consider it
Hi Chrissy, glad you liked it, thanks for commenting!
Nice Video, i was searching for this function for quiet long but couldn't find helpful articles or videos. One problem is, if there are multiple values in column a
Hi sharad upadhayaya, thanks for the comment!
Doug my hero!
☺️glad it helped!
Amazing
Thanks!
simply amazing!
Hi Mohamed Aboobacker Siddique, thanks for the comment!
what is the function of 2,1/ after lookup?
Could you maybe help please, I have the following situation: first value (product name) from the list, second value is the rating from the list and third value is the amount. My task: if i choose value 1 and insert rating, i want to calculate: the product with specific rating to result into respective number.
If i have product X and rating Y, take the amount Z.
Thanks 🙏
Do you have information on how to search if there are multiple possible answers or a video explaining it?
For example I'm searching for berries and the list is:
Strawberry
Pineapple
Mango
Blueberry
Blackberry
And I want those 3 words that contains "berry" to show up. Probably like a FILTER function.
Key Word Search to Bring Back Multiple Results
ua-cam.com/video/4S8WIf_Hskk/v-deo.html Maybe this would help
@@DougHExcelthank you so much this is the video I needed and it solved my problem😁
Fantastic! :)
Glad you like it!
I don’t understand 2,1/
what if the customer name had Enterprise Svcs, Delivery Logistics, & Resolute care all in the same field and you needed to extract only one of the customer names?
not sure what I am doing wrong but this is not working for me :( the lookup seems to be working because there are no returning values for most of my cells
its not working for me and i have one file can you help me out please
why is it 2/1 in look up value?
Thank you Doug H. How to add a command if the value is not available, result will be "others"?
maybe combine it with an IF function... ua-cam.com/video/YdEVo5X_Li4/v-deo.html
Hi, I was wondering if this formula is Case Sensitive?
Case sensitive lookup...hmm maybe these can help
ua-cam.com/users/DougHExcelsearch?query=lookup%20case
Can someone direct me where to read about how LOOKUP can handle an array if SEARCH cannot? Clearly by encapsulating SEARCH in LOOKUP an array is able to be handled, but what is the software architecture explanation that enables this? How does Excel allow an array to be passed into a function that doesn’t handle an array by itself but when it is nested inside a function that can handle arrays suddenly the nested function can handle the array?
I need help to convert one single column data into multiple raw with break line after cell color.
this might give ideas
ua-cam.com/video/5OF12HMxVuk/v-deo.html
ua-cam.com/video/OZbFPGwcyrE/v-deo.html
I know this might sound confusing but how can i assign one order containing description about 2 customers to both customers? thank you
you may want to us an OR function ua-cam.com/video/LOG9aNAAY34/v-deo.html
Dear Doug, How do we use return value incase there are similar sounding names, like resolute care and resolute carrier etc. Please guide.
At the end of the vid is a playlist for other lookup videos to give some ideas
Hello. Thanks for sharing. In the moment 2:12 you put number 2 in the formula. Why so? Thanks. Ps: I am trying to make my personal bank conciliation mode easy in identifying each figure automatically.
LOOKUP function is doing an approximated match, it won't find the number 2 but find the next approximate value (it's corresponding column match in that row) below the number 2.
@@DougHExcel Thanks a lot for your feedback.
@@DougHExcel Is there any way to contact you for sending a video doubt regarding the function LOOKUP. I have been trying to confirm if the operation I want to make is possible or not. Thanks again for your time and support.
I cannot type this function, it says "There's a problem with this forumula…." as soon as i type E2 in my forumula.
my bad, german exce uses different syntax, fixed it
Hi Sp0derman, thanks for the comment!
Why you entered 2,1 in that formula
The explanation begins at 4:00 in the video when going through the formula evaluator
Why my result is 0? Would you help? 😓
I keep getting 0 also
Alternative solution:
Load both tables to PQ
Create cross join
Add helper column ISNumber (Search.....
Filter rows for True
Hide unnecessary columns
Hi Matt, thanks for adding to the thread!