Excel Magic Trick 1352: Lookup 3 Items & Return to Single Cell VLOOKUP, INDEX or LOOKUP functions?

Download Files:

See how to create a formula that simultaneously looks up 3 item, joins then together in a single cell in order to produce a system code for CompanyName/PlantZipCode/. See 4 formula methods, including timing of calculation time for each formula:
1. (00:15) Introduction
2. (01:54) Formula 1: 3 VLOOKUP Exact Match functions
3. (04:45) Formula 2: 1 VLOOKUP Approximate Match function with Array Operation and the special keystroke Ctrl + Shift + Enter
4. (08:52) Formula 3: 1 INDEX & MATCH Approximate Match with Array Operation
5. (12:23) Formula 4: 3 LOOKUP Approximate Match functions
6. (15:12) Timing formula calculation time for each formula
7. (17:26) Ctrl + Shift + Enter: Mastering Excel Array Formulas Book
8. (17:38) Summary

Related Videos:
Excel Magic Trick 1282: Lookup And Return Multiple Items to a Single Cell: TEXTJOIN Function
Excel Magic Trick 901: Lookup First & Last Name From 2 Different Cells & Return Then To 1 Cell

[twright]

Mike, these Lookup examples are clever and innovative. Your tutorials with VLOOKUP along with the ROWS and COLUMNS functions (as the Column Index) are equally fantastic.

[nadermounir]

I enjoy the excel old school. Great tutorials Mike 👌

[entertainmentgalaxy]

You are excel wizard.... thanks for sharing

[MySpreadsheetLab]

Where is the 1000 thumbs up button? Like always thanks Mike!

[deepakagrawal]

Lots of learning and fun. Thanks a lot, Mike. That was amazing!! :)

[Luciano_mp]

Mike, fantástico, você é o cara. Obrigado.

[mohamedchakroun]

Thank you Mike looking forward to the next video

[scottpinkham]

great vid - I've learned so much from your thank you and keep it coming!

[IanPutt]

Very interesting thanks.  How about adding into column L a concatenation of GH&I, and then the simplest vlookup would work?

[rai_nav]

I am working on a project where column R has Yes or No. I need excel to tell give me value from column B And D when Column R has No. what formula should I use to do this. Excellent video btw. I am hoping to watch every single video wish me luck.

[janquin]

hello, how would you do a vlookup and display the values horizontally in a row? For example on the table that u have on the right hand side, you needed all the units l from the table on the left listed on a single row in asending order?

[hanysadek]

What if we use CONCATENATE function with VLOOKUP, does this save any time when we run the formula?

[abhaygadiya]

Can we use INDEX(TEXTJOIN(...), Match(...)). Also we can use Power Query - Merge queries features and then merge the columns to get result for this.

[BillSzysz]

Great video, thanks a lot !!
I am curious how fast is this formula below ( I can't check it, i haven't got TEXTJOIN in my excel)
=TEXTJOIN("-", , INDEX(($G$10:$G$18, $H$10:$H$18, $I$10:$I$18), MATCH(B4, $F$10:$F$18), , N(IF(1, {1, 2, 3}))))
Thanks again :-)

[adamtheboa]

hi my friend ive been told to research and learn excel can anyone point me in the right direction? thanks

[BillSzysz]

ooops...maybe shorter one
=TEXTJOIN("-", , INDEX($G$10:$I$18, MATCH(B4, $F$10:$F$18), ))