VLOOKUP function (lesson b)

Up Your Microsoft Excel Skills Formulas and Functions
3 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€88.46
List Price:  €123.85
You save:  €35.39
£75.37
List Price:  £105.52
You save:  £30.15
CA$138.20
List Price:  CA$193.48
You save:  CA$55.28
A$155.11
List Price:  A$217.17
You save:  A$62.05
S$129.96
List Price:  S$181.95
You save:  S$51.99
HK$774.96
List Price:  HK$1,084.98
You save:  HK$310.01
CHF 82.69
List Price:  CHF 115.77
You save:  CHF 33.08
NOK kr1,041.08
List Price:  NOK kr1,457.56
You save:  NOK kr416.47
DKK kr660.36
List Price:  DKK kr924.54
You save:  DKK kr264.17
NZ$167.99
List Price:  NZ$235.19
You save:  NZ$67.20
د.إ367.26
List Price:  د.إ514.18
You save:  د.إ146.92
৳12,188.92
List Price:  ৳17,064.98
You save:  ৳4,876.05
₹8,451.80
List Price:  ₹11,832.86
You save:  ₹3,381.06
RM426.95
List Price:  RM597.75
You save:  RM170.80
₦160,706.97
List Price:  ₦224,996.19
You save:  ₦64,289.21
₨28,187.10
List Price:  ₨39,463.07
You save:  ₨11,275.97
฿3,309.66
List Price:  ฿4,633.66
You save:  ฿1,324
₺3,845.79
List Price:  ₺5,384.26
You save:  ₺1,538.47
B$565.91
List Price:  B$792.30
You save:  B$226.38
R1,839.21
List Price:  R2,574.97
You save:  R735.76
Лв172.99
List Price:  Лв242.19
You save:  Лв69.20
₩139,976
List Price:  ₩195,972
You save:  ₩55,996
₪361.83
List Price:  ₪506.58
You save:  ₪144.74
₱5,550.44
List Price:  ₱7,770.84
You save:  ₱2,220.40
¥14,481.05
List Price:  ¥20,274.05
You save:  ¥5,793
MX$1,957.85
List Price:  MX$2,741.07
You save:  MX$783.22
QR364.06
List Price:  QR509.70
You save:  QR145.64
P1,361.73
List Price:  P1,906.48
You save:  P544.74
KSh12,964.33
List Price:  KSh18,150.58
You save:  KSh5,186.25
E£5,073.31
List Price:  E£7,102.84
You save:  E£2,029.52
ብር13,459.34
List Price:  ብር18,843.61
You save:  ብር5,384.27
Kz91,190.88
List Price:  Kz127,670.88
You save:  Kz36,480
CLP$94,946.49
List Price:  CLP$132,928.89
You save:  CLP$37,982.39
CN¥729.42
List Price:  CN¥1,021.22
You save:  CN¥291.80
RD$5,902.61
List Price:  RD$8,263.89
You save:  RD$2,361.28
DA13,288
List Price:  DA18,603.74
You save:  DA5,315.73
FJ$225.56
List Price:  FJ$315.80
You save:  FJ$90.23
Q770.30
List Price:  Q1,078.46
You save:  Q308.15
GY$20,924.33
List Price:  GY$29,294.90
You save:  GY$8,370.57
ISK kr12,940.45
List Price:  ISK kr18,117.15
You save:  ISK kr5,176.69
DH926.13
List Price:  DH1,296.62
You save:  DH370.49
L1,709.82
List Price:  L2,393.82
You save:  L683.99
ден5,441.97
List Price:  ден7,618.98
You save:  ден2,177
MOP$801.19
List Price:  MOP$1,121.71
You save:  MOP$320.51
N$1,867.19
List Price:  N$2,614.14
You save:  N$746.95
C$3,690.56
List Price:  C$5,166.94
You save:  C$1,476.37
रु13,561.38
List Price:  रु18,986.48
You save:  रु5,425.09
S/367.72
List Price:  S/514.83
You save:  S/147.10
K405.78
List Price:  K568.11
You save:  K162.33
SAR374.97
List Price:  SAR524.97
You save:  SAR150
ZK2,781.79
List Price:  ZK3,894.63
You save:  ZK1,112.83
L440.50
List Price:  L616.72
You save:  L176.22
Kč2,204.44
List Price:  Kč3,086.31
You save:  Kč881.86
Ft35,783.07
List Price:  Ft50,097.73
You save:  Ft14,314.66
SEK kr965.37
List Price:  SEK kr1,351.56
You save:  SEK kr386.18
ARS$117,540.76
List Price:  ARS$164,561.77
You save:  ARS$47,021
Bs693.19
List Price:  Bs970.49
You save:  Bs277.30
COP$426,824.69
List Price:  COP$597,571.64
You save:  COP$170,746.95
₡50,658.12
List Price:  ₡70,923.39
You save:  ₡20,265.27
L2,602.65
List Price:  L3,643.81
You save:  L1,041.16
₲803,267.16
List Price:  ₲1,124,606.16
You save:  ₲321,338.99
$U4,205.86
List Price:  $U5,888.38
You save:  $U1,682.51
zł378.54
List Price:  zł529.98
You save:  zł151.43
Already have an account? Log In

Transcript

Okay, now this video, we're going to do the V lookup multiple times. Basically, what we have is all of the states are listed here. The problem is we want to send out postcards to these vendors. And we can't do that with a mail merge, because we want to get the two character state abbreviation, the American abbreviation, okay. So we need a data set that it's going to reference, I happen to have it over here. So what I'm going to do is just do a quick copy of that.

And I'm going to paste it over here for just temporarily right there, okay. So, the first thing to do is insert a column where you'd like it to go just call it abbreviation And we are ready to rock and roll. So what it's going to do is going to look up Arkansas first, it's going to go down to find it. And then we're just going to want to reference the second position over. So this is going to be my table array. I look up each state and I'll be able to copy it down.

So it's super important that we absolute reference our table array structure here. To do that, it'll be dollar sign our dollar sign to colon dollar sign s, you know, all the way down to s 51. There. Okay, let's do it. So to begin, I'm going to select the Formulas tab, lookup and reference B lookup. Here we go.

My lookup value in this scenario would be Arkansas, e two. Okay. Then I'm going to tell it to look inside the table right for my next minute table. All right, I'm gonna do a little trick here, I'm gonna click on Alabama and use my Ctrl Shift arrow down on a PC Ctrl, Shift, arrow down, Control Shift, right. And then that gets me the data set. Now I'm going to hit f4 because it needs to be absolutely reference their column index number.

Again, think about it's going down, it's going to find state hit two over two. And our range lookup will be false. Just to prevent in case there's any Miss typing will get notified of that with an NA. So I'll hit OK. So far, like what I see I can copy it down. Also, if I wanted to just double click right there, that will copy it all the way down to the till it comes to the last row or a blank row.

Yeah, that is awesome. Awesome. Awesome. Awesome. Okay, so that is the V lookup, looking up multiple states.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.