How to use VLOOKUP on an entire column in Excel
In Excel, use the VLOOKUP function with a range automatically applied to an entire column.
In this example, you'll find a list of Company names based on Domain names to which they belong:
Formula in E12
=VLOOKUP(D12:D31,B4:C9,2,FALSE)
A
B
C
D
E
F
G
1
2
Column 1
Column 2
3
Domain
Company name
4
google.com
Google Inc
5
youtube.com
Google Inc
6
facebook.com
Facebook
7
twitter.com
Twitter
8
wikipedia.org
Wikipedia
9
instagram.com
Facebook
10
11
ID
Email
Domain
Company name
12
1001
viola@google.com
google.com
Google Inc
👈 Formula is here
13
1002
karilyn@youtube.com
youtube.com
Google Inc
14
1003
payten@facebook.com
facebook.com
Facebook
15
1004
felix@google.com
google.com
Google Inc
16
1005
jasper@twitter.com
twitter.com
Twitter
17
1006
hyrum@instagram.com
instagram.com
Facebook
18
1007
evony@google.com
google.com
Google Inc
19
1008
abraham@google.com
google.com
Google Inc
20
1009
xavier@youtube.com
youtube.com
Google Inc
21
1010
benjamin@wikipedia.org
wikipedia.org
Wikipedia
22
1011
damien@google.com
google.com
Google Inc
23
1012
brooke@wikipedia.org
wikipedia.org
Wikipedia
24
1013
carleen@facebook.com
facebook.com
Facebook
25
1014
orlando@youtube.com
youtube.com
Google Inc
26
1015
finn@wikipedia.org
wikipedia.org
Wikipedia
27
1016
reeve@google.com
google.com
Google Inc
28
1017
lydon@twitter.com
twitter.com
Twitter
29
1018
harriet@facebook.com
facebook.com
Facebook
30
1019
trevor@wikipedia.org
wikipedia.org
Wikipedia
31
1020
ace@google.com
google.com
Google Inc
32
Show arrows
Separator
Procedure
- Enter =VLOOKUP in cell E12, where you want the Company names to appear.
- Enter the range D12:D31 as the Lookup value, which contains the list of domains that you want to look for.As the Lookup value is a range, VLOOKUP returns a Company name for each row of the range automatically.
- Enter the Search range B4:C9, the data range containing the Domain and the Company name values.
- Enter Column number 2 as the Company name column is the 2nd column of the Search range.
- Enter FALSE to look for an exact match.
Result
The final formula is: =VLOOKUP(D12:D31, B4:C9, 2, FALSE)
For each row of the Domain names, VLOOKUP returns the Company names to which they belong.