Excel Magic Trick 1499: VLOOKUP or INDEX Joins to Create Transaction Description (3 Examples)
HTML-код
- Опубликовано: 25 июл 2024
- Download Excel File: excelisfun.net/files/EMT1499-...
Entire page with all Excel Files for All Videos: people.highline.edu/mgirvin/ex...
In this video learn how to create a description for a transactional data set that requires that we lookup items to join with the Transaction Number. See how to use the Ampersand, the join operator, and how to use the lookup functions VLOOKUP function and INDEX & MATCH functions.
1. (00:06) Introduction
2. (01:20) Two VLOOKUP functions in single formula solution
3. (04:33) Join Column in Lookup Table Solution (and one VLOOKUP) solution
4. (05:52) Array Formula with INDEX and MATCH functions with joined lookup columns in an Array Operation and an Array Formula.
5. (09:32) Summary
![Cordae - Saturday Mornings (feat. Lil Wayne) [Official Music Video]](http://i.ytimg.com/vi/z2GYJua09F0/mqdefault.jpg)
I prefer the Index/Match function because it's more flexible to use when you have multiple data sources. Thank you for the great videos!!
I like all of them. When you have an amazing teacher, everything is fun to learn. Oh! by the way, I found the solution for the Power BI question I asked you yesterday. Thank you for helping me out Mike.
Glad all of this helps and is fun!
Thanks Mike. The first Vlookup and the last Index and Match were the most fun. :) :)
Thanks, John : )
Great tricks! Thumbs up!
Thanks for your support : )
Awesome job Mike!
Glad you like it, Chris!!!
Always awesome!
Glad it is always awesome : ) Thanks for your support, tartarus216!!
I really like the index and match function. I need to use it more. Thank you for making it fun. Also power query would had work
Yes, INDEX and MATCH are good. Power Query would be good if data was imported and needed to be refreshed often.
Great .....
Very useful information...
Glad it is useful! Thanks for the support : )
Legal Mike, obrigado.
Glad you like it, Luciano!!! Thanks for the support : )
EXCELlent. Thanks Mike.
You are EXCELlently welcome, Syed!!!! Thanks for the support : )
From watching your excellent videos I have come to prefer INDEX-MATCH over VLOOKUP.
But inspired by your recent "How to Allocate Invoice Headers" series I did it in PowerQuery using = 'Fact'[Transaction Number]&": "&RELATED(Lookup[Supplier])&" - "&RELATED(Lookup[State]). Thanks for the great lessons!
Yes, that is awesome!!!! Thanks, Rob : )
Nice trick. I like INDEX method, it's new for me, it's like we building new table for array parameter from separate column or separate table.
Yes, building an formula internal array is sometimes just what you need. Thanks for the support, Jung!!!
Great as usual
Thanks, RRR!!!!
Very useful tricks, thank you, Mike :)
You are welcome, Teammate!
:)
Thanks Mike.
You are welcome, WRH!!!
Very useful sir
Glad you like it! Thanks for the support : )
Nice review on index :-)
Review is always good. Thanks for the support : )
Love it. Thanks as ever.
You are welcome as ever!!! Any preferences, Oakley?
And I thought I know everything there is to know about INDEX :):):) Thank you soooo much for another fantastic video!
You are welcome so much!!!!!
ExcelIsFun Mike, I feel star struck! :). I was following you for years. I owe to you my advanced Excel skills and I insist to watch your videos to every of my colleagues. Everybody knows my obsession with “Excel is Fun” in my office, so every knows your RUclips name. :). You truly have a gift of teaching! Thank you sooooo much!
You are welcome! Thanks for helping to share the free education of excelisfun with everyone : )
Never used index and match but it looks fun to use. I will try it. Power query would also work for this
Yes, INDEX and MATCH are good. Power Query would be good if data was imported and needed to be refreshed often.
Very Nice video
Glad it is nice for you! Thanks for the support, Ashok!!!
Lovely and this help me to make fright calculation by country and city.
Glad the video helps!
I love you man!! ..#NoHomo
I love you too!!! Love and Excel are good : )
Good day mike!newbie here..need help..i just want to ask if how can i assigned multiple list of names into 3 specific person and should be distributed also in to 7 teams but each team have different count of names and may randomly change..do you already have a video about this?thnks!
Thank you very much for your video.
Personally, I prefer index-match combination; I always use it instead of vlookup.
Because it is more flexible in terms of the lookup value; it does not have to be at the first column; in many cases, I encounter that. With Vlookup, the lookup value has to be at the first column in the lookup table.
No doubt, INDEX and MATCH are the most flexible!
Thanks a lot for this amazing video I really got use of it, I work as an accountant and although there's an accounting software I work on, but I often use Excel in my daily work tasks. I would be glad if you could advice or recommend a series of yours that I can follow up which enables me to produce accurate results in a shorter time. very much appreciated
The best full Excel Series I have is this (25 videos with Excel files, practice problems and pdf notes below each videof or download): ruclips.net/p/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
Even though this is a Basics Class it covers everything is perfect order. After that class, the advanced class is here: ruclips.net/p/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
Be sure to support this free resource with a Thumbs Up on Each, Comments and a Sub.
ExcelIsFun thanks a lot for your support.
You are welcome!
I liked the INDEX with the joined arrays.
Array Formulas are fun : )
As already stated from others before, index-match combination allows to avoid the trouble of the left column issue of vlookup - I didn't know, it seem fun and I am going to use it. Thanks on this!. However, I would like to see near soon the opposite, where you need to extract specific info (usually in the middle) from a column or more, without applying text to columns. In that cases I go for a combination of vlookup + find + len + mid/left/right but for sure I am rather curious how else can someone succeed the same result. Keep on indeed it is fun.
Yes, INDEX and MATCH! I have posted a few videos on this topic over the years, looking up something from the middle.
This looks like the beautiful beginning of a new series, so I’m gonna cut through the chase here: my preferred method is PQ to make the helper column in the dProduct table and then one RELATED in DAX to finish it of. :-)
I”ll admit, it’s a bit of a case of using a cannon to hit a small target, but there it is. ;-)
And in this video: because of the above: the single VLOOKUP solution with the helper column in dProduct (which is calculated only once - take that array calculation!).
I’m a bit partial towards my old friend VLOOKUP, because of all the hard work we’ve done together in years past. :-)
Well, my old friend, I’m affraid I have a bit of bad news for you: in case of (not so) big data you will be replaced by PQ and a bit of DAX, because I found out that we’re RELATED. ;-)
And all of that is Mike’s doing...
I’m kidding of course! In this case: one VLOOKUP. But somehow I feel that Mike is gonna prove me wrong and make me see things differently in ways I don’t realize just yet. Ah! The anticipation!... :-)
What a show this channel is! A real treat. So, I’m lookin’ forward to the next one.
This the only video on this topic.
ExcelIsFun OK.
vlookup is easier for me to understand. I like the index function because it can handle arrays and is much more flexible.
Thanks for your thoughts! And thanks for the support, Chris : )
Thanks to your's and couple other channels on youtube, I have been referred to as the "Excel Guru" around the office. I'll take it. :)
Yes, that is I post here at excelisfun: so we can become more efficient, save time and helps others at the office : ) P.S. I can hear it now at your office: Chris "Excel Guru" Demeur!!!!!
Mr. Mike, you're exceptionally good and outstanding in the way you teach excel with fun. Pls, I want to make a request. My humble request is that, is it possible to have all your excel lecture videos from 0001 to the current series in one or two DVD for beginners to learn from scratch. I will appreciate to have your kind response on this request. Thank you.
I am sorry, but I do not have such a dvd for you. The best I can do is provide them for free here at RUclips : (
@@excelisfun OK sir. I will appreciate if all can be provide as playlist on RUclips. Thank you. More grace, knowledge and understanding in God's name.
The vlookup with the side column on the look up table was best. Arrays and index are always elusive! What is an array after all? You said when it has two operators or something. I never understood that part. Is it an array because of the "&" coming twice in the index formula?
No, Array Operations are simple!!!!!!!
2*2 is not an array operation. 2&2 is not an array operation. 2>15 is not an array operation. "Joe"&"Downing" is not an array operation.Why, because on either side of the operator is one single item.
2*{2,3,2,9} is an array operation. 2&{2,3,2,9} is an array operation. 2>{2,3,2,9} is an array operation. {2,3,2,9}>{2,3,2,9} is an array operation. {2,3,2,9}+8 is an array operation. All of these have more than one item on one of the sides of the operator.
Another way to think about it is 2* 3 = 6. The operation yields is a single answer. But 2>{3,1,0,5} yields a resultant array of answers: {FALSE,TRUE,TRUE,FALSE}. This does too: 2+{3,4,1} = {5,6,3}.
I have great videos about this topic and complete playlists also.
Good video: ruclips.net/video/RDP1uF7HafU/видео.html
Great Playlist: ruclips.net/p/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci
Please help support this channel with Thumbs Up : )
Just did a thumbs up
Thanks for sharing the link
Thank you sir : ) It helps on all the videos : )
I will go for Index match, because a) No need of product always be 1st left most column b) No need of helper column in source data as we need in 2nd option of Vlookup c) Don't have to worry about changing of 3rd argument(column Index number) of Vlookup, if in between some columns are inserted or deleted d) I believe It will be much faster compare to vlookup in case of big database.
Good thinking! Although I think that INDEX and MATCH vs. VLOOKUP would be about the same calculations speed.
Very nice as always, was TEXTJOIN considered? =C5&": "&TEXTJOIN(" - ", TRUE, VLOOKUP(D5, $K$5:$P$20, {2, 5}, 0)) which does require CSE :)
Love it! Great formula : ) : )
Pls,.my request is on the Excel Magic Trick from 0001 to the current number in one or two DVD that one can buy. Thank you
I have a "TEXT" and I want to search that specific text in the worksheet and get the cell address of that specific text using an excel formula, how can I do it, please help if it's possible !
Formulas like these might work to lookup a word in a column and return cell address:
=CELL("address",INDEX(B3:B11,MATCH(D3,B3:B11,0)))
="B"&MATCH(D3,B:B,0)
=ADDRESS(MATCH(D3,B:B,0),COLUMN(B2),4)
=ADDRESS(MATCH(D3,B3:B11,0)+ROW(B2),COLUMN(B2),4)
D3 has lookup value
B2 has field name of column of data
B3:B11 has data
I prefer index match, although other methods are good too
Thanks : )
I love formulas really because I'm trying to learn how to do macros it doesn't work with me at all
I am a formula guy too. : ) : )
I am an index-match convert. I try not to do vlookup when possible.
Cool! Thanks for your preferred method! Why do you prefer INDEX and MATCH over VLOOKUP?
*I think * it's possible to use the =concatenate function as well to pull information from your table with the vlookup
Also, great video! I learned a lot about the index function. Might try to use it in my future work 😀
Yes you can use CONCATENATE. Good point, Patrick. However, for me, since concatenation is used so often, especially in SUMIFS and COUNTIFS formulas, I use the Ampersand, &, because it makes the formula easier to read, shorter in length and there are not as many calls to functions. Many years ago, i just taught myself to always use ampersand and not use CONCATENATE to get those three benefits in each Join Formula that I create.
Yes, INDEX can do many amazing tasks.Thanks for your comments, Thumbs Up and Sub, Patrick : )