Tỷ giá ngoại tệ

Highline Excel Class 40: VLOOKUP 11 Unusual Examples



Download Excel Start File 1:
Download Excel Start File 2:
Download Excel Finished File:

See these 11 VLOOKUP tricks:
1.VLOOKUP algorithm
2.VLOOKUP, Named Ranges, Exact Match, COLUMNS function& Data Validation List
3.Commission Calculation: VLOOKUP, Approximate match, 3 VLOOKUPS in one formula
4.VLOOKUP with TRIM function to remove spaces from first column of lookup table, add zero to convert text back to number
5.VLOOKUP with TRIM function to remove spaces from lookup value
6.VLOOKUP and Ampersand to create Product ID
7.VLOOKUP with Table on Different Sheet
8.VLOOKUP and MID function to extract part of text string for the lookup value
9.Partial Text VLOOKUP (Exact Match) with wildcards
10.Calculate Currency Exchange with VLOOKUP Into Currency Rate Web Query
11.VLOOKUP appears incorrect because of Formatting

This is a beginning to advanced Excel class taught at Highline Community College by Mike Gel excelisfun Girvin Busn 214 BTech 109

Nguồn: https://mywifi.com.vn/doi-song

Xem thêm: https://mywifi.com.vn/doi-song/ty-gia-ngoai-te/

36 Bình luận

  1. Data Scientist of Life

    Sir when I am applying this particular formula it returned -VLOOKUP(F7,TRIM($A$8:$B$29),2,0) – #Value I couldn't understand why is it come.. table Names : The Data Dump has Spaces — Sir Is there any problem in my excel..
    Sir I solved the same I made mistake after entering the formula instead of enter ALT+SHIFT+ENTER I enter CTRL+ENTER .. now its not been a problem you resolved while listening your videos again..Thanks..

  2. misfit

    If existing connections does not have an FX rates how can I add into existing connections?

  3. eka sulava

    Great Job! 

  4. RRRR

    At 23:50, you don't need all the "*" in front of the cell reference. So =VLOOKUP(A13&"*";A$7:A$10;1;0) is sufficient.

  5. DOON

    Thank you very much for this informative vid. I was trying to use =IF formulas. Even with nesting I couldn't get the results I wanted.. =VLOOKUP is so much better for what I was trying to achieve. Cheers!

  6. Ismael Bakayoko

    Hey Mike i need help with an excel problem. My Vlookup-value is a range of number. My question is how can i put a range of # as a lookup value. for example # (1 to 4) refer to Student and # (6 to 8) refer to Professor .

  7. ExcelIsFun

    I am glad that you liked it!

  8. ExcelIsFun

    That is VERY useful!!

  9. Gail Mackinnon

    Hi Mike. Just watched the video and I'm keen to work through it but I can't find the file using your link. Help!!

  10. ExcelIsFun

    I am glad that you liked the video!

  11. Edras E Moran

    Great work Mike, you are the man!

  12. ExcelIsFun

    I will post a video about a solution for you next Friday, Jan 25!

    You never sent me an e-mail???

  13. ExcelIsFun

    Judi,

    Making a few assumptions about what you need, I have come up with a solution and will make a video and post it this Friday. The formula looks something like this:

    =SUM(VLOOKUP($H3,ProductTable,2,0),VLOOKUP(I$2,PackageTable,2,0))*$G3*(1-VLOOKUP($G3,DiscountsTable,2))

    You can still post to that other site and then send me the link, or I can send you a copy of the workbook I have created by sending me your e-mail addrress through my YouTube e-mail.

  14. ExcelIsFun

    I am unclear about what exactly you are trying to do. For back and forth dialog to get Excel solutions, try THE best Excel question site:

    mrexcel [dot] com/forum

    After you post you can send me a YouTube e-mail with the link to your post and I can take a look. However, many others will probably also try to answer your question.

  15. ExcelIsFun

    Well… at least we are here at YouTube!

  16. AgentCell

    Wow Mike. I wish you were here teaching in NYC.

  17. ExcelIsFun

    @arofairy , I am glad that the videos help!!

  18. ExcelIsFun

    @hersheng18 , you are welcome!

  19. ExcelIsFun

    You are welcome!

  20. ExcelIsFun

    I thought SUMPRODUCT and SUMIF would do that. I do not have a good answer for you. Try posting your question to THE best Excel site:

    mrexcel[dot]com/forum

  21. Nawabi

    @ExcelIsFun Thank you for the link, but it's not exactly what I was looking for. With sumif/sumproduct, you have to insert your criteria, what to look for and the info to retrieve. I was just hoping to use vlookup to look for a value and retrieve the sum of everything found in the table with the value searched. Not sure how I am?!?!

  22. ExcelIsFun

    Try this video:

    Excel Magic Trick 320: Lookup Adding: SUMPRODUCT & SUMIF

  23. Nawabi

    Thank you very much for your videos, you explain them very well. I've been youtubing for a while now trying to find a specific function of the vlookup. Say for example, you're looking up the TOTAL value of all bills sent to a client (e.g. client A) so the table array would include client A more than once if more than 1 invoice was sent. How do I get VlookUp to retrieve the SUM of all bills sent to client A, not just the value of one of the bills? Thank you 🙂

  24. ExcelIsFun

    I do not know. But no problem, try this site:

    mrexcel..com/forum

    I am sure one of the 100+ Excel gurus there will have a good answer!

    After you post, send me the link so that I can follow along and learn also!

  25. ExcelIsFun

    I am glad that it is helpful!

  26. moazzamca1

    The one with wild card was awesome.

  27. ExcelIsFun

    Can you send me the workbook with an explanation of where the problem is and what you expect the correct answer to be:

    excelisfun at gmail

    I'll take a look.

  28. ExcelIsFun

    Yes. If you enter the criteria W* for a field that contains West and Win, it will extract all records that contain West and Win.

  29. Wal Ee

    HI, just a quick question:
    does the * sign also works when using it in defining the criteria for the advanced filter?

  30. ExcelIsFun

    I have no idea how to do that in Excel. I think Access can do it, but I do not do videos on Access.

  31. JERRY ROAD

    hi I am looking for vlookup for pictures, is there a tutorial for that. thanks a lot

  32. Sandy G

    Hi,

    I was thinking (as we have already downloaded the startfile), it will be a good idea for learners, if we are given some challenges/exercises to work on at the end of tutorials.

    Thanks.

  33. ExcelIsFun

    Yes, this one was particularly fun to make!!

  34. microsuez

    Very comprehensive!

  35. ExcelIsFun

    Dear maxandbrener,

    Me too! I love VLOOKUP! As Mr Excel posted at a jobs site to advertise himself:

    "Can do VLOOKUP in my sleep!"

    If you know how to use VLOOKUP it is easier to get a job!

    –excelisfun

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *