Grab the file I used in the video from here 👉 pages.xelplus.com/reverse-matrix-lookup-file

boy you are a genius! awesome!!!

love it! this was really helpful.

This video saved my life. Very well explained, and so useful to walk through a matrix

Thanks for the INDEX and MATCH fun with Reverse Lookup : )

@LeilaGharani

7 жыл бұрын

You're very welcome Mike :)

@Gkdi872

4 жыл бұрын

I BENEFITTED A LOT FROM THE REVERSE LOOKUP EXAMPLE. BEEN WORKING ON A WORK THAT HAS TAKEN ME MORE THAN 2 WEEKS TO SOLVE AND NOW I AM USING THE REVERSE LOOKUP CHEERS AND THANKS SO VERY MUCH!

@raghuv7114

4 жыл бұрын

I Love you all. Thanks for your unconditional love and exploring excell knowledge. My favorite Mike sir and Leila Madam.you are all great inspiration to me and the only reason to learn and improve my excell skills to another level.

@excelisfun

4 жыл бұрын

@@raghuv7114 Go Team!!!!!

@raghuv7114

4 жыл бұрын

@@excelisfun Mr.Excell page may be old and Greatest. My big guide page may be the best But Leila gharani (Xelplus) Madam and Mike Girvin Sir ( excelsfun) will always be my favourite pages forever 😍❤️❤️❤️❤️❤️.

God Bless you.. whenever i need to do any task in excel which is given by my employer. I find the solutions in your videos.. You are really doing a noble act.

@LeilaGharani

4 жыл бұрын

It's great to hear the knowledge from the tutorials can be applied at your work.

@HBraz97

4 жыл бұрын

I'm employing this on a spreadsheet in my work as well! Thank you so much Leila!

Very useful trick.Thanks a lot.

Thank you! You explained it so well

Very helpful, thanks! :)

This is an excellent tutorial, as always. How would you go about listing all categories where values in rows are not unique for a product? For example, D for product 2 is in categories 2 and 3.

thank you, Leilah, as always simple and briliant.

@LeilaGharani

7 жыл бұрын

You're very welcome & thank you!

Great explanation and formula. Thanks.

Thank you Leila.

Really awesome example of reverse lookup in index & match. Thank you so much Leila .

@LeilaGharani

7 жыл бұрын

You're very welcome Sanjay :)

Brilliant your method is amazing.,👍

thank you very much for this :)

Thanks a Million

Thanks laila for good presentation, Your presentation are always important. :)

You made my day

Superb i was searching for this and finally found this thanks a lot.

Thank you. This really helps me ease my job.

Thank you so much for this support! Have you ever produced the same result in power bi as a dax measure?

Wow, thank you for the solution. Usually this requires an array. Excellent work!

@LeilaGharani

6 жыл бұрын

You're very welcome. True - I tend to enjoy finding non-CSE solutions :)

Mind blowing.. Very good explanation and presentation.

@LeilaGharani

7 жыл бұрын

Glad you like it Ismail.

excellent!!!!!

Enjoyed the video. Looking forward to applying.

@LeilaGharani

7 жыл бұрын

Glad to hear that. Yes...applying is important....

way of teaching is amazing.... keep up the good work. Thank you so much

@LeilaGharani

7 жыл бұрын

Thank you Ved & you're very welcome.

Thank you, very interesting lesson for reverse lookup.

@LeilaGharani

7 жыл бұрын

You're very welcome.

Thank you, I always appreciate your insights and clear presentation!

@LeilaGharani

4 жыл бұрын

Thanks for the kind feedback Eric.

@elslagle

4 жыл бұрын

@@LeilaGharani You're most welcome, and deserving, Jedi. I'll be back for more lessons soon...

Thanks a lot for the lessons. God bless you.

@LeilaGharani

7 жыл бұрын

You're welcome Samuel.

Very nice step by step explanation

@LeilaGharani

7 жыл бұрын

Thanks Vida.

I really learn a lot of EXCEL from your video. Much appreciated !

@LeilaGharani

7 жыл бұрын

Very happy to hear that! You're very welcome.

One of The Best Channel to learn excel from KZread . Please do upload few Dashboard Videos

@LeilaGharani

7 жыл бұрын

Thanks for the support Kapil! Appreciate it. Sure. Will certainly do.

Thanks madam your all excel trick and techniques very helpful.very thankful to you

@LeilaGharani

6 жыл бұрын

You're very welcome Praveen. Thank you for your comment and support.

Become a great follower of urs . Simply awsome .

@LeilaGharani

7 жыл бұрын

Thanks :) & appreciate the support.

Very Nice Mam

@LeilaGharani

5 жыл бұрын

Glad you like it.

Thank you so much, an awesome video. What would happen in a scenario where you are the same value in a row but in different categories, e.g A in both Category 1 & 7 for Product 1 Also in my own scenario, I have different categories with the same name, how would I adjust the formula for best function?

Thanks Leila for such a nice tutorial! Cool technique. Great idea!!! Well, when ever it is a question of choosing a range from several choices; CHOOSE comes to my mind first. So I tried with that. It is little longer but easy to understand. First, CHOOSE & MATCH - gives the Row according to the Product. Then, MATCH finds the Column and thus INDEX finds the Header (Category) =INDEX(B3:G3, MATCH(B10, CHOOSE(MATCH(B9,A4:A6,0),B4:G4,B5:G5,B6:G6), 0) )

@LeilaGharani

6 жыл бұрын

You're welcome Mahbub. I like your choose and match approach. Also using the match inside choose to get the index number. Thank you very much for sharing!

Awesome Mam, thank you for saving my week, very easy for basic understanding may God Bless you

@LeilaGharani

9 ай бұрын

Glad to hear that!

Leila, This is great! Thank you. But what if I wanted to look up the row from matrix and column data?? Is that possible??

thanks

Excellent courses, Laila

@LeilaGharani

6 жыл бұрын

Glad you like them Saad.

HI Leila, thanks for the pointers. I'm struggling to use the same approach for finding the row header instead of the column header. How would that work?

Nice job

@LeilaGharani

7 жыл бұрын

You're welcome Abdelrahman.

QQ- would it be possible, if the array had distinct values, to look up BOTH the column and row headers for a value in the matrix?

Thanks leilaaaa

@LeilaGharani

7 жыл бұрын

You're very welcome.

I've made this formula before watching your video ***=CHOOSE(IF(B9="product 1",MATCH(B10,$B$4:$G$4,0),IF(B9="product 2",MATCH(B10,$B$5:$G$5,0),IF(B9="product 3",MATCH(B10,$B$6:$G$6,0)))),B3,C3,D3,E3,F3,G3)***, I thought I should've tried this myself before I saw your video and got the answer without using my brain, I got the answer on my own but my formula is longer than yours :)

This is very helpful. From your example, what if you need to find and list multiple "categories" from 2 types in 1 product? Example: for Product A, you have type "A" for Category 1 and also type "A" for Category 2. How can you list down Category 1 and 2 with type A?

thanks Dear

@LeilaGharani

7 жыл бұрын

You're welcome :)

interesting video

Thank you so much. This works for me in excel, but getting an error in google sheets. Any suggestions would be much appreciated.

What if I just want to keep “select product” option in the drop down list and want to get a list of column headers in a list which ever have data in it?

Just had a chance to look into your video while in a search for my excel requirement. This video is excellent and it almost match my requirement. Really appreciate the way of your narration with example. I need your help in modifying the formula based on my requirement. Lets say, in your example, what if more than 1 Category have the same Alphabet. For example, what if Both Category 1 and Category 3 is having "A". Hope my question is clear. Would really appreciate if you could help me to derive the formula. Thanks in advance.

What about the case where the values are unique? How do you set up the match?

Thank you Leila. I find Max value in a table and want to find reverse which row and which column?. Please give your suggestion.

Hii, will this work with multiple worksheets?

Mam i have not seen ur videos for some days due to some reasons but i have come back..... nice tutorial

@LeilaGharani

7 жыл бұрын

Hello Jacky. Good to see you back :)

This was really helpful, thanks!!! I do have a question though. What if I wanted excel to return the header of the last occurrence of a repeated value? Say for example that D was repeated and appears in category 4 and 5 and I would want excel to give the header of the last repeated occurrence, in this case that D is in category 5? Would really appreciate your help on this.

@jacobcardiello9803

3 жыл бұрын

Hi Oscar, Did you ever figure out a workaround? Having the same issue. Thanks in advance.

Leila, how would you switch this around if you wanted to find column for (Category3) and knew the type (B) and then get the answer Product 2?

If value come same, for example for Product 2 and select type D will come 2 times then how we show it?

I think this is the best "how to" use "index and match" I have seen in the 5 years i have been using it (prior to this it has always been a combination of 50% luck and 50% magic if it worked ;)

@LeilaGharani

3 жыл бұрын

Glad it was helpful, Michael.

is there a limitation on the numbers of columns to look for? I'm traying this method and my categories go from B1 to EN1 (143 columns). My rows are listed from A1 to A20721. Thanks!

you are good

@LeilaGharani

6 жыл бұрын

Thanks Ankur - glad you like the videos.

What if A repeat in a row? How to find both categories? Also how to eliminate #N/A in cells that does not meet the criteria?

Thanks for your videos :-) I have a similar problem but not sure how to adjust to suit. How would I return the column header for a product category. Assume that Product Categories are stages i.e. the furthest column (left to right) which contains something will be the stage it is at. I have a separate sheet with the products listed out and I would like to reference which stage that product is at. Sorry if unclear :-/ Any help appreciated.

Thanks cool video and very informative. is it possible to have two identical number on a single row please?

@LeilaGharani

7 жыл бұрын

You're welcome Ola. If you have identical numbers on the rows as well, you will get the first match...

Yet, another brilliant hack.

@LeilaGharani

7 жыл бұрын

Thanks Syed :)

Thanks Leila, would like to know how to retrieve multiple categories if same type in "A" in Category 1 and Category 3

@Praetorian269

4 жыл бұрын

I have the same question. Doesnt look like she answers questions here.

How do you go about with multiple results column header?

Thanks for your effort In case that we have duplicate values in our arrays, for example if we have "A" for product 1 & category 1 and "A" for product 1 & Category 2 ?

@LeilaGharani

6 жыл бұрын

It will find the first one only. To find all the matches, you can use the Aggregate function - a technique similar to this one: kzread.info/dash/bejne/malo2qyQcazHnJs.html

@raoufabualsondos6306

6 жыл бұрын

Awesome, THANK YOU :)

how did u do the tutorial while appearing yourself?what apps u used?

Leila! I need exactly that, but I only need to find the headers. And the values are unique. How can I do that?

How do we do this if the product appears in more than one category? Say, if D appeared in more than one category of the same product.

@naveenkumar-to7wr

4 жыл бұрын

Did you hit the solution for this query?

I need help to find specify logic function in excel that will match certain value in a column and cell and if the value match then it will look the immediate left cell and if there is a "1" in that cell then it will go to the column header and get the column header in differ worksheet . If the immediate left cell is empty then it will loop thorough that row and find value "1" and get the column header .

Hi, How can I do that with only 1 criteria For exemple I want to get D but I have only column with product?

How do we find the row and column headers if the values are unique?

This is a nice spin on a two way lookup. I like how you ignored the row argument then did a row lookup inside the columns argument. that is really a neat trick. Can you provide use with a practice file so we can slice and dice this to some of our projects at work? You present so many fabulous ideas in your videos, like Mike does, but I can' remember all of them.

@LeilaGharani

7 жыл бұрын

Hi Michael :) glad you like it. Thanks for your comment. Yes, I will definitely upload the workbook. Normally I try to set up the link to the workbook at the same time as when the video goes live. Didn't manage this time, but I'll have it up in a bit and will let you know.

@LeilaGharani

7 жыл бұрын

I just added the link to the workbook Michael.

@michaeldiamond2726

7 жыл бұрын

Thank you Leila. Since it was a small dataset I just copied it and mimicked your formula. It actually helped me learn it faster by typing it in. I wish I had a photographic memory and could instantly remember all the cool techniques you show us. How long does it take you to normally shoot a video like this - 2 or 3 hours (between all the redos and edits)?

@LeilaGharani

7 жыл бұрын

You're welcome Michael. Currently they take around 4-5 hours. But it's just practice right? The more videos I do, the easier it gets. It's just that doing video is completely different to "live" training. I was never a "natural" for the camera, so it takes time to feel comfortable :)

@michaeldiamond2726

7 жыл бұрын

Ah, you just being polite. You look quite natural in the video. I'm sure there are many out there who agree with me, right?

Perspicuous mam...Mam plzz plzz plzz upload a video on Absolute and relative cell referencing too

@LeilaGharani

6 жыл бұрын

I've added this to my list Ubaid. Will certainly do :)

@UbaidUllah-nj5et

6 жыл бұрын

Thank you mam...looking forward to learn this complicated topic from u mam :)

i have a similar problem: basically a "matrix-like" sheet in which i have application names on rows, and enterprise functions on columns. The two elements are matched if an x is present in the cell in which they encounter. there might be more than an "X" on a single application row (more than one enterprise function. I NEED to have function names instead of Xs. is there a way?

Hi, Could you please advise if there is a way to search for a string in a cell. I have a situation, where I have master data of user email addresses - their personal and professional ones in a single cell, separated by comas. And other columns with their name, company name, and other data. And I have a transaction data with the transaction id and user email address. Now, I have to check, if the emails given in the transaction are from the existing values in the master data. Do you have a solution to help me here, please? Thanks for your great work! :-)

can we do the lookup in reverse like (normal lookup moves from left to right) using lookup but it does searching from right to left? I it possible.

@LeilaGharani

5 жыл бұрын

Best choice for that is Index Match. You can check it our here: kzread.info/dash/bejne/eGZqlqipcqe-Yps.html

I have data in an matrix of form 20*20 . My objective is to choose any 6 no. and sum them and find the combination which has a sum equal to a certain no. how can I do it can u help me.

Thanks! If i have datasets with 3 columns (product, category, status {available,sold}) and i have to display all sold products, is the index and match still applicable? 😀

@LeilaGharani

5 жыл бұрын

You will probably need index and aggregate (i have a video on finding multiple match results on the channel) easier would probably be pivot or power query and possibly soon dynamic arrays.

@aisabrine

5 жыл бұрын

@@LeilaGharani thanks! Will on check on the video. 😀

It was difficult can we use concantrate

i want A to tell me what product and category

what if i need to find product instead? how can i do this

Hi I need your help plz. Can you give me a link (if you have a vdo about my request) where it shows the combination where the A is found. example in this case, the A is found in Category 1 - Product 1, Category 4 - Product 2 and Category 5 - Product 3. A formula to show where is found from a multiple choice.

What happens if Type appears multiple times in the same row?

Hello Leila, I have a ques. If to create a list of Country --> States --->> District --->>> Cities --->>>> Towns and Villages So what all formulas should i apply . i am facing trouble in this, So i'll wait for you Suggestion

@LeilaGharani

6 жыл бұрын

Hi Ankur, you could create a dependent drop-down list - I'll be uploading another video on dependent drop down lists soon where your original data is in tabular format which is the likely format you'll have the master data in. You could apply that technique to your problem.

Great approach! But what if the table is big and you don't know which Type belongs to which product? This formula will help to list the Types specific to particular product in a row: =INDEX(Reverse!$B$4:$G$6;MATCH(Reverse!$B$9;Reverse!$A$4:$A$6;0);COLUMN(Reverse!$A$1:$F$1)):INDEX(Reverse!$B$4:$G$6;MATCH(Reverse!$B$9;Reverse!$A$4:$A$6;0);COLUMN(Reverse!$A$1:$F$1)-1) Formula must be put in Names Manager under assigned name, "Type" or whatever and then in data validation.

We can also use following =INDEX($B$3:$G$3,,SMALL(IF($B$4:$G$6=B10,IF($A$4:$A$6=$B$9,COLUMN($B$3:$G$3)-COLUMN($B$3)+1)),COLUMNS($D13:D13)))

Dear Madam, I am very much thankful to you for the reverse look up video. I found it very much useful for one of my problem. I would like to request you to provide me your e mail id. I have a doubt in this relation, which i will send you across.

interesting points ,if anyone else wants to discover cell phone number lookup try Niposcu Fast Phone Adviser (Have a quick look on google cant remember the place now ) ? Ive heard some awesome things about it and my m8 got cool results with it.

Madam I want speak to you

You are A Miss of Excel.

@LeilaGharani

6 жыл бұрын

Thank you for the compliment Boban.