Excel Magic Trick 1170: VLOOKUP To Different Table In Each Column: CHOOSE & COLUMNS Functions

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

See how to:
1. (00:09) Problem Setup
2. (01:40) COLUMNS Function with expandable range to get Sequential Numbers across the columns
3. (02:17) CHOOSE Function to look up different tables
4. (03:35) VLOOKUP with CHOOSE and COLUMNS to get different price from different lookup tables based on Column that formula sits in.
  1. excelisfun
  2. Highline College
  3. Mike Girvin
  4. excelisfun
  5. Michael Girvin
  6. Mike excelisfun Girvin
Рекомендации по теме
Excel Magic Trick 0:04:33

Excel Magic Trick 1170: VLOOKUP To Different Table In Each Column: CHOOSE & COLUMNS Functions

Excel Magic Trick 0:08:46

Excel Magic Trick 1234: VLOOKUP to 36 Different Tables: Helper Column w LOOKUP & Expandable Rang...

Excel Magic Trick 0:13:14

Excel Magic Trick 1174: Two Lookup Values For VLOOKUP? How Do I Do That? (6 Methods)

Excel Magic Trick 0:05:38

Excel Magic Trick 1233: VLOOKUP to 36 Different Dynamic Tables: Table Feature & INDIRECT Functio...

Excel Magic Trick 0:14:10

Excel Magic Trick 1100: Cross Tabulated Lookup: 1) Lookup Row then match or 2) Array Multiplication?

Excel Magic Trick 0:08:22

Excel Magic Trick 1177: Flash Fill or CODE Formula Split First Last Name Based on 2nd Capital Letter

VLOOKUP on Multiple 0:10:35

VLOOKUP on Multiple Columns or Tables

Excel Magic Trick 0:05:48

Excel Magic Trick 1172: Multiple Dot Plots With Single Formula, INDEX, COUNTIFS & REPT

Excel Magic Trick 0:07:11

Excel Magic Trick 1169: Are Items In Row In Master List? Filter & Conditionally Format To Highli...

Excel Magic Trick 0:01:19

Excel Magic Trick 968: Highlight Formula Element with Screen Tip

Excel Magic Trick 0:09:49

Excel Magic Trick 1136: Correlation From 4 Columns: CHOOSE Array Formula “Mash” Columns Together

Excel Magic Trick 0:03:06

Excel Magic Trick 1171: SUMIFS & SUMIF Treat 0500 & 00500 the Same! Use SUMPRODUCT Instead

VLOOKUP Across Multiple 0:08:28

VLOOKUP Across Multiple Tables CHOOSE & VLOOKUP

Working with files 0:07:59

Working with files VLOOKUP function in Excel 2010 Column fill

Excel Magic Trick 0:07:51

Excel Magic Trick 1164: Average Score After Subtracting Two Lowest: SMALL & Array or AVEAGEIF?

Excel Magic Trick 0:09:53

Excel Magic Trick 1175: Decaying Average in Standards-Based Grading in Excel

Excel Magic Trick 0:04:47

Excel Magic Trick 1165: Substitute Final Score If Greater Than Low Score: IF, MIN & SUM Function...

Using Excel's SWITCH 0:06:24

Using Excel's SWITCH Function to VLOOKUP Multiple Tables

Excel Magic Trick 0:09:20

Excel Magic Trick 1129: Aging Accounts Receivable Reports Using Slicer, Excel Table, Page Setup

How To Use 0:01:58

How To Use VLOOKUP With MATCH Function In Excel

VLookup To The 0:01:47

VLookup To The Left WithThe Choose Function - Excel Tips

Excel Magic Trick 0:03:03

Excel Magic Trick 1041 Expandable Range In An Excel Table For Running Total Zack Barresse's Tri...

VLOOKUP with Multiple 0:06:38

VLOOKUP with Multiple Lookup Tables choose function

Excel Magic Trick 0:06:16

Excel Magic Trick 1033 Re do Duel 134 Join Product and Sub Product Text Using PivotTable

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

Excel Magic Trick 1170: VLOOKUP To Different Table In Each Column: CHOOSE & COLUMNS Functions
See how to:
1. (00:09) Problem Setup
2. (01:40) COLUMNS Function with expandable range to get Sequential Numbers across the columns
3. (02:17) CHOOSE Function to look up different tables
4. (03:35) VLOOKUP with CHOOSE and COLUMNS to get different price from different lookup tables based on Column that formula sits in.

excelisfun
Автор

Mike You are awesome ..thanks for all the videos

tonyt.
Автор

Two thumbs up on this trick- very nice

PERALTASTONES
Автор

CHOOSE is great !  Thanks for sharing Mike.

MySpreadsheetLab
Автор

Mike, you are the best. Thanks so much for what you do.

Jim-zmfw
Автор

As Kevin Lehrbass said - CHOOSE is great!

pmsocho
Автор

your an excel ninja...i need a stem cell transplant from your brain, so I can be a wizard too!

kurtturner
Автор

hm what about typing this formula in E2 and just dragging it over the green cells ?
=VLOOKUP(B2, $I:$J, 2, 0)

imho the outcome is the same

ido
Автор

Can someone please tell me why I'm coming up with a zero percentage below????  Is it because I'm asking excel to count the same column twice? "External Referral - Sys Admin = 10", "Issue Forwarded to Lexis = 2", "COUNTA = 130" so the percentage for now should be

=COUNTIFS(Sheet1!K:K, "External Referral - Sys Admin", Sheet1!K:K, "Issue Forwarded to Lexis")/ COUNTA(Sheet1!A:A)

mjeg
Автор

Out of interest why not just three different vlookups in each of the columns with the array fixed to the relevent lookup table.


Matt-ptvq