Highline Excel Class 40: VLOOKUP 11 Unusual Examples

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

Category:

Education

Tags:

• Excel
• VLOOKUP
• function
• formula
• TRIM
• COLUMNS
• MID
• Mr
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[arofairy]

I m really grateful, no doubt you are excellent teacher!!! you are my teacher, salute you!!!!! great work!!!

[ExcelIsFun]

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

[hersheng18]

these infos are really very helpful. thank you so much.

[ExcelIsFun]

@hersheng18 , you are welcome!

[gnossticc]

"A great tool always needs a great teacher"

This is for you Mike, thanks!

I hope you are feeling much better from your wrist!

[ExcelIsFun]

You are welcome!

[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

[maralghawsi]

@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?!?!

[ExcelIsFun]

Try this video:

Excel Magic Trick 320: Lookup Adding: SUMPRODUCT & SUMIF

[maralghawsi]

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 :)