Excel INDEX / MATCH Function for an Advanced Lookup

[@Computergaga]

The XLOOKUP function now makes this task even easier - kzbin.info/www/bejne/qYXNZH6kl6x5nq8

[@revanvonheaven8270]

Hey mate can I reach you about the counting cards and lottery numbers I hope I can help you try doing this get a notebook 📔 and record all the numbers 4 times a week twice for all 12 months in a row could you be able to help me calculate 5 probable combos in the duration period of time of the next 12 months next year yes or no?.

[@mightyhunter7849]

Thumb Up sir . my question is : how did you convert match formula in row number? on the time 9:55 plz reply

[@Computergaga]

Thanks Hafiz. After highlighting the MATCH part of the formula, I pressed F9 on the keyboard. This manually calculates that part of the formula. Really useful when testing and fixing formulas.

[@enochnanduru]

@@Computergaga Seriously, you are Master Jedi, all excel based honor and glory be unto you Sir.

[@miguelgaitan1630]

Hello, I'm very interested in taking a course in Xlookup, indexmatch, formulas

[@Computergaga]

Sure. There are course links in the video description. There is also my 'Advanced Excel Formulas' book with over 500 formula examples - amzn.to/3Rg87Go

[@chinmayeesingh9040]

Till today i cd not understand the actual funda of match n index actually

[@anelimrimo]

I'm taking an excel class and I didn't understand my teacher, but I understand you. Thanks so much.

[@Computergaga]

Great to hear. Thank you Milena.

[@marktimson1485]

Thanks for a very easy to understand video. A question for you if you can help. How do you use index match to ignore a blank cell? For example, a data table of football matches to be played still, no scores in the scores column but in the fixture grid the scores all show as 0-0, when the scores are entered in the data table it shows in the fixture grid correctly. So how can get the fixture grid to show nothing(blank) instead of 0. This is the formula i have showing in the home team on the fixtue grid...{=INDEX(Fixtures!$G$4:$G$555,MATCH(Sheet1!$C6&Sheet1!$D$4,Fixtures!$F$4:$F$555&Fixtures!$I$4:$I$555,0))}. But like I said it shows 0 instead of nothing if no score is entered.

[@kvlpnd]

2:50, " I don't wanna hurt its feelings " 😂😂

[@Computergaga]

:)

[@SivadasKRamdas]

Thanks Buddy, this video helped me a lot to solve one excel problem i was facing.

[@Computergaga]

Great to hear. Thanks Sivadas.

[@4nnbnn]

Thank you so much for your help!Really detailed and well explained!

[@Computergaga]

Thank you.

[@keithtomalin3172]

Great Video... Sooo much better than Vlookup. Well done and thank you.

[@Computergaga]

You're welcome Keith.

[@etpollylifts]

God bless you. Thank you for this tutorial you saved me

[@Computergaga]

Excellent. Happy to help Ethan.

[@novellez]

vlookup is much easier and quicker

[@Computergaga]

True. I got a lot of love for VLOOKUP. The INDEX & MATCH combo offers a more flexible alternative that is often not required, but awesome when needed.

[@ihormaza1]

Thank you for your detailed and clear explanation. It was very helpful!

[@Computergaga]

You're welcome, thank you.

[@arifurrahman4407]

Sir, Thank you so much for your most useful tutorial.

[@Computergaga]

My pleasure. Thank you Arifur.

[@notawix8542]

now there is X loockup

[@Computergaga]

There is indeed - kzbin.info/www/bejne/qYXNZH6kl6x5nq8

[@RB-os7qw]

Do you do online courses?

[@Computergaga]

I certainly do. You can find my courses and books at computergaga.com

[@alexrosen8762]

Just awesome! Thanks a lot :-)

[@Computergaga]

You're welcome. Thanks Alex.

[@artlogo1881]

Is there a search button that can produce 3 results, A button you press ?

[@gerardvaneggermond9967]

Hello, maybe you can help me, I have an Excel sheet, in which I have 9 cells, I have this in Sheet 1, in sheets 2 and 3 I have data where I want to retrieve things and this must meet certain conditions, it is a lot, how do I do that?

[@Computergaga]

Merge Queries is one of the easiest ways - kzbin.info/www/bejne/p3Tbh4CGatikqrs

[@posfr292]

For years I resisted Excel Tables instead of old-school ranges and INDEX-MATCH instead of VLOOKUP. Now, having used Power Query, I'm totally converted - born again. With Tables, and using "structured references", the MATCH and INDEX functions write themselves and become self documenting. If you convert your range to a Table and name the table COURSES, the first of your formulas becomes something like =INDEX(COURSES[Company], MATCH(4202, COURSES[Course ID], 0))

[@Computergaga]

Yes tables are fantastic. There are still some things for Microsoft to iron out to make them perfect, but a big improvement in many ways especially dynamism, structured references and then the use of the Power Tools.

[@youlilgremlinkun]

Thank you for the video! One question: Is there a benefit to a two-way lookup function? When you brought it up, I wasn't clear on why it was beneficial to have a Match for the column. I was thinking in my head "Why wouldn't we do what we did earlier and select the specific column as the table array?" I feel like I'm missing out on how it can be useful in some scenarios, so if anyone has an answer, I'd appreciate it! Thanks again for the video, much appreciated for the help!

[@808BLAddict]

KnockEmmm I had the same question.. it seemed less complicated to just do a one way look up and it looks as if it would give you the same results... if anyone can explain the benefits of a 2 way I would appreciate it too!

[@mohamedmami2480]

Great! Thanks a lot Alan..

[@Computergaga]

Your welcome Mohamed.

[@lucmorineau6741]

Excellent video and explanation, thank you so much! I made some update to your proposal to use tables, also working well :)

[@Computergaga]

Brilliant. Thank you Luc.

[@waynebrown8366]

Couldn't you have used a simple INDEX/MATCH to find Number of Delegates instead of a 2-way I/M as well?

[@Computergaga]

Yes absolutely. Just wanted to demonstrate a 2 way lookup so that we did not need to specify the exact column.

[@granand]

Sir How should I list all the companies offering the course ID, the list should be in same cell appended by "," comma.

[@Computergaga]

I would use VBA. The unknown quantity of companies make using a formula and concatenating them awkward.

[@mrbuthelezi7121]

i have been struggling with this function for a while, the n i watched this video and attempted the problem i had at work once. never been this happy in a long time Thanks man ☺•o☺♂♂o♂♂

[@Computergaga]

Great to hear. Thank you, Vincent.

[@willisjwhhbjrjrb]

After watching countless of videos and searching all over and reading articles, the logic only made sense with your wording. Thank you.

[@Computergaga]

That is nice to hear. Thank you for your comment.

[@iheartbunnies1121]

I haven't commented on any videos for so long but just wanna say tysm sir u saved me a lot of trouble and ur a real g

[@Computergaga]

Thank you very much.

[@saifalislam5987]

Have been watching some other videos on this topic , but this video explained it so well , Thanks!

[@Computergaga]

You're very welcome! Thanks Saif.

[@KaldaneAnswers]

Nice Video. I always like the way you explain things

[@Computergaga]

I appreciate that, thank you.

[@ashutoshdwivedi9600]

Super knowledge sir Still I'm waiting your reply in mail

[@Computergaga]

Thank you Ashutosh.

[@gabrielcastillo4126]

Thank you so much for your videos!

[@Computergaga]

You're welcome, Gabriel.

[@sandeshmainali7045]

Thank You for the video :)

[@Computergaga]

My pleasure, Sandesh.

[@angelkz7427]

Thank you! Just what I've been looking for!

[@Computergaga]

You're very welcome Angel.

[@quocvietdang8118]

Hay

[@cipher2]

Excellent excel tutelage channel!!!

[@Computergaga]

Thank you very much.

[@jackychawla5422]

Sir please add some complexity

[@Computergaga]

Jacky, your a beast. I will try.

[@jackychawla5422]

Computergaga sir please cover dis topics once for all

[@j.d.r2049]

Very useful

[@Computergaga]

Thank you Vishwa.

[@mikejaoude5496]

Anyone ever have an issue where when you choose your array you don't use entire columns (so you do for example A2:C11) and the data you get out is always a row off? For example if I do this: =INDEX(A2:C11,MATCH(G3,A:A,0),MATCH(H2,A2:C2,0)) It always gives me a row lower than what the value should be for cell G3. But if i do this, it is correct: =INDEX(A:C,MATCH(G3,A:A,0),MATCH(H2,A2:C2,0)) Do you always have to run with full rows/columns?

[@Computergaga]

Hey Mike, you don't have to use entire columns. But they need to start from the same place. The first MATCH function - MATCH(G3,A:A,0) will count from row 1 because it is given A:A. But INDEX is returning from a range beginning A2. The MATCH function doesn't return the row number. It returns the index number from where it found the value. Hope this helps explain.

[@mikejaoude5496]

@@Computergaga thanks for the quick reply. That was actually super helpful. So I changed the row Match from A:A to A2:A:11like you said and it worked! You're the best thanks!!

[@Computergaga]

My pleasure Mike.