Excel Lookup Series #8: MATCH Function

[hersheng18]

i've been watching almost of your videos. great job!

[ExcelIsFun]

@hersheng18 , I am glad that you find them useful!

[burceavc]

hi

[burceavc]

hi

Please can you help me with a table, I try for 3 days and no success

I have this table

Cable Type Length



[burceavc]

AR-12-89

AX-22-20

BS-12-38

AX-22-6

AX-9-200

DD 300

[burceavc]

I want to return a table like this

AR-12-total 89

AX-22-total 20+6=26

AX-9-total 200

BS-12-total 38

DD total 300

Thank you in advance

Vio Romania

[alpinepatriot]

Every tutorial, simply amazing... Thank YOU!

[ExcelIsFun]

You are welcome!

[eapeters12]

aside from your fantastic teaching, you've got a great sense of humor too. Its a pleasure to learn from you!!

[ExcelIsFun]

I am glad that it helped!

[MrSabstar]

WOW ....................GREAT TEACHING

[ExcelIsFun]

I am glad that you like the video!

[quintinpm]

Good day, - Question; I have 3 columns in a spreadsheet, if I choose an item in column 1 (drop down menu)I would like an option in column 2, then by choosing my choice in column 2 it will then give me a choice in column 3. So eg: column 1 stationary, toiletries column 2 for stationary the choices would be; pen, ruler, ink choose pen, then column 3 should give me an option black pen, blue pen. Thank you

[ExcelIsFun]

Try these video titles to have a selected items change the next data validation drop-down list:

YTLE#75: DV Drop-Down based on DV Drop-Down

Excel Magic Trick # 259: Dynamic DV List Based On DV List

[ExcelIsFun]

Try these videos if you want the values to actually show up in cells:

Excel Magic Trick 400: Show List of Values In Cells After Using Data Validation Drop-Down

Excel Magic Trick 324: Dynamic Data Validation List of Dates

However, these last two videos are not the best way to go if you want cell values to change. In this case you may want to post a question to the Mr Excel Message Board and ask for a VBA solution:

mrexcel[dot]com/forum

[bahebbak]

I have a report, that download Order from an orer managment systems. These orders have order type, which is the 5 categories I mentioned ( DS,DT,DX,FD,FX). Everyday, I have to sort them, and fill them in the subtabs(DT,DX,DS,FD,FX). I would like to automate this process, by creating a master tab, has all records, and have this mastertab updates the subtabs, if the amount of data download changes, the subtabs data shall change acordingly. Thank you very much for everything.

[ExcelIsFun]

Try the Mr Excel Message Board at:

mrexcel[dot]com/forum

[bahebbak]

Awesome Videos. Thank you very much for sharing your knowledge, and donating your time like that.

I have a question, if you could answer it for me, pplease?

I have 5 data catagories, DS,DT,DX,FX,FD, come in a report that varies in size everyday, but always contain these types, with alot of other data in the report.

Could I have the whole report as a mastertab, and make it autofill subtabs, named for each type?evrytime, I insert the new data, the subtabs changes accordingly?

Thank youagain

[ExcelIsFun]

I do not understand your question. You can try to re-state it. Or you can post it to the Mr Excel Message Board (THE smartest Excel people in the world hang out at this site):

mrexcel[dot]com/forum

[83Percy]

Hi again,

Is there any other funtion then MATCH that will lookup an approximate value and round UP?

Thanks again for a great Youtube channel!

[ExcelIsFun]

Dear 83Percy,

Not that I know of. But if you want to ROUND, try these videos:

Excel Magic Trick #237: ROUND to Digit or Multiple

Excel Magic Trick #238: Round Times To 5 Minutes

Excel Magic Trick #41: ROUNDDOWN/UP CEILING/FLOOR functions

--excelisfun

[vrrusa]

you lifesaver! ,0! WOW!

Can you help me? I need to search all open workbooks for a value? Now I copy & paste the No. into find & choose a cell in each open workbook over & over. A desktop LookUp column would ROCK!

I use 2003 & when they updated our acct system, they changed EVERTHING & made no references. I need to search 3 or 4 huge worksheets for each invoice so I can compare line items & see if they match!

I want to keep this job so I figure if anyone knows how, its you!

[ExcelIsFun]

Dear vrrusa,

If you are asking how to do it with a formula, I have no idea. But the Find and Replace cammand will do it. If you select all sheets in workbook then find will look in all sheets.

VBA code would do it, but you would need to post a question to the Mr Excel Message Board. The Smartest Excelers in the world hang out there. Watch this video:

How To Post Questions At The Mr Excel Message Board

--excelisfun

[shiryu008]

hmm, the Match function. In spanish version is =coincidir(.....).

Ok, let head for the next video.

[ExcelIsFun]

Dear shiryu008,

Total cool! I have no idea about non-english functions. MATCH = COINCIDIR!!!!

--ExcelIsFun

[angrychef1]

Thank you for posting all your videos. I'm going through your lookup series and have learned at lot. Your instructions are clear and all those little shortcuts and tips are awesome!!!!

[ExcelIsFun]

Dear angrychef1,

I am glad that this is helping! I'll keep making the videos if you keep watching and rating!!

--ExcelIsFun