Excel Magic Trick 1356: Lookup 3 Items & Return to Single Cell Helper Column or Power Query?

preview_player
Показать описание
Download Files:

See how to lookup 3 Items & Return to Single Cell with a Cell Helper Column or Power Query:
1. (00:15) Introduction
2. (01:19) Cell Helper Column in Lookup Table solution. Trick thanks to Ian Putt At YouTube.
3. (02:50) Power Query (Get & Transform) solution with Merge Feature and Custom Column to Join items from Lookup Table. Trick thanks to Abhay Gadiya at YouTube.
4. (11:19) Summary

Also see this video:
Excel Magic Trick 1352: Lookup 3 Items & Return to Single Cell VLOOKUP, INDEX or LOOKUP functions?
  1. excelisfun
  2. Excel
  3. Microsoft Excel
  4. Highline College
  5. Mike Girvin
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:11:46

Excel Magic Trick 1356: Lookup 3 Items & Return to Single Cell Helper Column or Power Query?

Excel Magic Trick 0:13:38

Excel Magic Trick 1415: Power Query Lookup and Return Multiple Items And Join Them In Single Cell

Excel Magic Trick 0:17:10

Excel Magic Trick 1359: Split Times Values Into 8 Equal Zones: VLOOKUP, LOOKUP or INT/HOUR?

Excel Magic Trick 0:08:43

Excel Magic Trick 1508: Lookup In Current Table? Self Join? VLOOKUP or Power Query?

Excel Magic Trick 0:18:07

Excel Magic Trick 1352 Lookup 3 Items & Return to Single Cell VLOOKUP, INDEX or LOOKUP function...

Excel Magic Trick 0:10:33

Excel Magic Trick 1086: 3 Way Lookup Formula & Conditional Formatting, Date Criteria Mismatch

Excel Magic Trick 0:01:34

Excel Magic Trick 1358: Query Dependencies View in Power Query (Get & Transform)

Excel Magic Trick 0:26:34

Excel Magic Trick 1128: Aging Accounts Receivable Reports On Multiple Sheets With Array Formula

Excel Magic Trick 0:24:51

Excel Magic Trick 1326 Conditionally Format Text Modes PivotTable, Array Formula or Power Query

Case Sensitive Lookup 0:05:48

Case Sensitive Lookup Formula - Exact Match Lookup in Excel

Excel: creating Email 0:02:34

Excel: creating Email lists using Left function, &, and Lower function

Excel Magic Trick 0:30:37

Excel Magic Trick 1322 Backwards One To Many Relationship Report Excel, Power Query or SQL

Excel: Case Sensitive 0:06:34

Excel: Case Sensitive Lookup Using EXACT.

FILTERXML Function to 0:05:08

FILTERXML Function to Split Text into Cells. Excel Magic Trick 1685.

Excel SUBSTITUTE function 0:03:04

Excel SUBSTITUTE function trick

Import Power Pivot 0:03:36

Import Power Pivot Data Model into Power BI Desktop (Excel Magic Trick 1365)

Excel University Volume 0:06:44

Excel University Volume 2 - Chapter 8 Moving Beyond VLOOKUP with INDEX - Solutions

Sobolsoft com How 0:01:39

Sobolsoft com How To Use Excel Change Case to Proper, Upper, Lower & Sentence Software

EXACT Function in 0:03:38

EXACT Function in Excel

How to Use 0:03:17

How to Use Excel String Function - Len Function

Microsoft Excel - 0:06:20

Microsoft Excel - Advanced Formulas and Functions Tutorial | Password Generating Workbooks

Excel get the 0:00:40

Excel get the last letters of text in a cell

Use the SMALL 0:02:57

Use the SMALL Function in Excel

Excel Formula - 0:03:16

Excel Formula - Upper, Lower, Proper

Комментарии
Автор

Revised version of this formula vlookup :)


im not sure since when textjoin joined excel rank but I'm glad it did!

ExcelInstructor
Автор

Thank you for sharing your amazing expertise in Excel! Love your video, which provide excellent knowledge improvement in Excel. Keep up the good stuff. Have improved amazingly with your tips. Big Thank you!

chuckhamdan
Автор

Hey There! Excellent video as always! Though i just want to say that in the Company-Zip-ProductID formula inside PowerQuery, you could also use the Text.From([PlantZipCode]) to transform those numbers into text and with that, the user don't need to format data on tables orinside querys anymore. I think PQ got alot of usefull functions that not everyone knows about, but that makes our life (excel users) way way less painfull! :) tanks for your awsome vids buddy!

LegendaryBullStrike
Автор

Hello. Good day. I would like to ask on how will I look up the value within min and max range?

For example: I have to look up the zipcode's zone with min code of 4 and max of 8 as 2.the only given data is 4 and 8 which is the min and max but my data is within the range of 4 and 8. Example is 5. So how will i print 2 if I the only given value is 4 to 8 and the approximate value is 5?

Thank you!

catherinelabro
Автор

Respected Sir,

Your amazing Excel Classes is so good for my bright career ... I learn new tricks from your beautiful effort and it help me find better job..

BIG THANKS form bottom of heart

Dev

Dev_Bartwal
Автор

I learn a lot from your videos. Great help for me. thank you!

edito
Автор

I love your videos! it's a way to learn and understand easily!

karlalobo
Автор

I agree with Jonathan, merge column feature also lets you pick a delimiter and no need to remove source columns after. Great video!

afedianine
Автор

Thank you Mike, No problem it is important to know all the alternatives and at work we use the most efficient or the appropriate one :-)

mohamedchakroun
Автор

yes there is a merge column feature you can use that instead of writing long formula.
Thanks Mike your vedios are really helpful

nononsense
Автор

Thanks for you, Great Video it's helpful for me!!!

muhammadfathi
Автор

Thanks. Another good video. Note: there is a merge columns feature on the transform ribbon.

jonathancooper