VLOOKUP Formula

[Harbor1704]

Hi everyone,
I have been trying to find an answer and have used multiple formulas to no avail I have two tables (County and City) and have two queries (County and City). I was trying to do a dropdown list of county and city in D5 and D13 however I was not succesful to get vlookup to work. So what I did was just do vlookup with a wildcard and trim without the lists, however it kept giving me an error. So now it is just a wildcard lookup however, when there is nothing in the county or city the abstractor defaults to the first cell of the table.

If someone could help me do lists and then write a vlookup formula to do a wildcard or exact match that would be awesome and greatly appreciated

VA Abstractor Query.xlsx
	D	E	F	G	H	I	J	K	L	M
1						VA City	Abstractor		VA Counties	Abstractors
2	County Abstractor Query					Alexandria	Independence Title, Jefferson Title		Accomack	Shore Title, LLC
3						Bristol	No Abstractor on File		Albemarle	Greater Richmond Abstract, Jennifer Coffey
4	County	Abstractor				Buena Vista	No Abstractor on File		Alleghany	No Abstractor on File
5		Shore Title, LLC				Charlottesville	Jennifer Coffey		Amelia	Powhatan Title
6						Chesapeake	Eastern Virginia Title		Amherst	Trinity Title
7						Colonial Heights	Powhatan Title, Greater Richmond Abstract		Appomattox	Trinity Title
8						Covington	No Abstractor on File		Arlington	Independence Title, Jefferson Title
9						Danville	No Abstractor on File		Augusta	No Abstractor on File
10	City Abstractor Query					Emporia	No Abstractor on File		Bath	No Abstractor on File
11						Fairfax	Independence Title, Jefferson Title		Bedford	Trinity Title, Gail Browning
12	City	Abstractor				Falls Church	Independence Title, Jefferson Title		Bland	No Abstractor on File
13						Franklin	Droll's Express		Botetourt	Trinity Title, Gail Browning
14						Fredericksburg	Independence Title, Terry's Title and Abstract		Brunswick	No Abstractor on File
15						Galax	No Abstractor on File		Buchanan	No Abstractor on File
16						Hampton	Taylor Abstract, Droll's Express		Buckingham	Jennifer Coffey
17						Harrisonburg	No Abstractor on File		Campbell	Trinity Title
18						Hopewell	Powhatan Title, Greater Richmond Abstract		Caroline	Terry's Title and Abstract, Greater Richmond Abstract
19						Lexington	No Abstractor on File		Carroll	No Abstractor on File
20						Lynchburg	Trinity Title		Charles City	No Abstractor on File
21						Manassas	Independence Title, Jefferson Title		Charlotte	No Abstractor on File
22						Manassas Lake	No Abstractor on File		Chesapeake	Droll's Express
23						Manassas Park	Independence Title, Jefferson Title		Chesterfield	Powhatan Title, Greater Richmond Abstract
24						Martinsville	No Abstractor on File		Clarke	Independence Title
25						Newport News	Taylor Abstract, Droll's Express, Eastern Virginia Title		Craig	No Abstractor on File
26						Norfolk	Droll's Express		Culpeper	Independence Title, Terry's Title and Abstract
27						Norton	No Abstractor on File		Cumberland	No Abstractor on File
28						Petersburg	Greater Richmond Abstract		Dickenson	No Abstractor on File
29						Poquoson	Taylor Abstract, Droll's Express, Eastern Virginia Title		Dinwiddie	Powhatan Title, Greater Richmond Abstract
30						Portsmouth	Droll's Express, Eastern Virginia Title		Essex	Terry's Title and Abstract
31						Radford	No Abstractor on File		Fairfax	Independence Title, Jefferson Title
32						Richmond	Powhatan Title, Greater Richmond Abstract		Fauquier	Independence Title, Terry's Title and Abstract
33						Roanoke	Suffolk Title, Gail Browning		Floyd	No Abstractor on File
34						Salem	Suffolk Title, Gail Browning		Fluvanna	Jennifer Coffey
35						Staunton	No Abstractor on File		Franklin	Gail Browning
36						Suffolk	Taylor Abstract, Droll's Express, Suffolk Title		Frederick	Independence Title
37						Virginia Beach	Droll's Express		Giles	No Abstractor on File
38						Waynesboro	No Abstractor on File		Gloucester	Droll's Express
39						Williamsburg	Taylor Abstract, Droll's Express		Goochland	Powhatan Title, Greater Richmond Abstract
40						Winchester	Independence Title, Jefferson Title		Grayson	No Abstractor on File
41									Greene	Jennifer Coffey
42									Greensville	No Abstractor on File
43									Halifax	Trinity Title
44									Hanover	Powhatan Title, Terry's Title and Abstract, Greater Richmond Abstract
45									Henrico	Powhatan Title, Greater Richmond Abstract
46									Henry	No Abstractor on File
47									Highland	No Abstractor on File
48									Isle of Wight	Taylor Abstract, Droll's Express, Suffolk Title
49									James City	Taylor Abstract, Droll's Express, Greater Richmond Abstract, Eastern Virginia Title
50									King and Queen	Greater Richmond Abstract
51									King George	Terry's Title and Abstract
52									King William	Powhatan Title, Greater Richmond Abstract
53									Lancaster	No Abstractor on File
54									Lee	No Abstractor on File
55									Loudoun	Independence Title, Jefferson Title
56									Louisa	Powhatan Title, Terry's Title and Abstract, Jennifer Coffey
57									Lunenburg	No Abstractor on File
58									Madison	Terry's Title and Abstract, Jennifer Coffey
59									Mathews	Droll's Express
60									Mecklenburg	No Abstractor on File
61									Middlesex	No Abstractor on File
62									Montgomery	No Abstractor on File
63									Nelson	Jennifer Coffey
64									New Kent	Powhatan Title, Droll's Express, Greater Richmond Abstract
65									Norfolk	Droll's Express
66									Northampton	Droll's Express, Shore Title, LLC
67									Northumberland	No Abstractor on File
68									Nottoway	No Abstractor on File
69									Orange	Terry's Title and Abstract, Jennifer Coffey
70									Page	No Abstractor on File
71									Patrick	No Abstractor on File
72									Pittsylvania	Trinity Title
73									Powhatan	Powhatan Title, Greater Richmond Abstract
74									Prince Edward	No Abstractor on File
75									Prince George	Powhatan Title, Greater Richmond Abstract
76									Prince William	Independence Title, Jefferson Title
77									Pulaski	No Abstractor on File
78									Rappahannock	No Abstractor on File
79									Richmond	Terry's Title and Abstract
80									Roanoke	Trinity Title, Suffolk Title, Gail Browning
81									Rockbridge	Trinity Title
82									Rockingham	No Abstractor on File
83									Russell	No Abstractor on File
84									Scott	No Abstractor on File
85									Shenandoah	No Abstractor on File
86									Smyth	No Abstractor on File
87									Southampton	Droll's Express, Suffolk Title
88									Spotsylvania	Independence Title, Terry's Title and Abstract
89									Stafford	Independence Title, Jefferson Title, Terry's Title and Abstract
90									Surry	Droll's Express
91									Sussex	Droll's Express
92									Tazewell	No Abstractor on File
93									Warren	Independence Title
94									Washington	No Abstractor on File
95									Westmoreland	Terry's Title and Abstract
96									Wise	No Abstractor on File
97									Wythe	No Abstractor on File
98									York	Taylor Abstract, Droll's Express
Sheet1

Cell Formulas
Range	Formula
E5	E5	=VLOOKUP(D5&"*",Table1,2,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hajiali]

I would use Index Match instead of the array vlookup try this

WorkbookY.xlsm
	D	E	F	G	H	I	J	K	L	M
1						VA City	Abstractor		VA Counties	Abstractors
2	County Abstractor Query					Alexandria	Independence Title, Jefferson Title		Accomack	Shore Title, LLC
3						Bristol	No Abstractor on File		Albemarle	Greater Richmond Abstract, Jennifer Coffey
4	County	Abstractor				Buena Vista	No Abstractor on File		Alleghany	No Abstractor on File
5		Shore Title, LLC				Charlottesville	Jennifer Coffey		Amelia	Powhatan Title
6						Chesapeake	Eastern Virginia Title		Amherst	Trinity Title
7						Colonial Heights	Powhatan Title, Greater Richmond Abstract		Appomattox	Trinity Title
8						Covington	No Abstractor on File		Arlington	Independence Title, Jefferson Title
9						Danville	No Abstractor on File		Augusta	No Abstractor on File
10	City Abstractor Query					Emporia	No Abstractor on File		Bath	No Abstractor on File
11						Fairfax	Independence Title, Jefferson Title		Bedford	Trinity Title, Gail Browning
12	City	Abstractor				Falls Church	Independence Title, Jefferson Title		Bland	No Abstractor on File
13		Independence Title, Jefferson Title				Franklin	Droll's Express		Botetourt	Trinity Title, Gail Browning
14						Fredericksburg	Independence Title, Terry's Title and Abstract		Brunswick	No Abstractor on File
15						Galax	No Abstractor on File		Buchanan	No Abstractor on File
16						Hampton	Taylor Abstract, Droll's Express		Buckingham	Jennifer Coffey
17						Harrisonburg	No Abstractor on File		Campbell	Trinity Title
18						Hopewell	Powhatan Title, Greater Richmond Abstract		Caroline	Terry's Title and Abstract, Greater Richmond Abstract
19						Lexington	No Abstractor on File		Carroll	No Abstractor on File
20						Lynchburg	Trinity Title		Charles City	No Abstractor on File
21						Manassas	Independence Title, Jefferson Title		Charlotte	No Abstractor on File
22						Manassas Lake	No Abstractor on File		Chesapeake	Droll's Express
23						Manassas Park	Independence Title, Jefferson Title		Chesterfield	Powhatan Title, Greater Richmond Abstract
24						Martinsville	No Abstractor on File		Clarke	Independence Title
25						Newport News	Taylor Abstract, Droll's Express, Eastern Virginia Title		Craig	No Abstractor on File
26						Norfolk	Droll's Express		Culpeper	Independence Title, Terry's Title and Abstract
27						Norton	No Abstractor on File		Cumberland	No Abstractor on File
28						Petersburg	Greater Richmond Abstract		Dickenson	No Abstractor on File
29						Poquoson	Taylor Abstract, Droll's Express, Eastern Virginia Title		Dinwiddie	Powhatan Title, Greater Richmond Abstract
30						Portsmouth	Droll's Express, Eastern Virginia Title		Essex	Terry's Title and Abstract
31						Radford	No Abstractor on File		Fairfax	Independence Title, Jefferson Title
32						Richmond	Powhatan Title, Greater Richmond Abstract		Fauquier	Independence Title, Terry's Title and Abstract
33						Roanoke	Suffolk Title, Gail Browning		Floyd	No Abstractor on File
34						Salem	Suffolk Title, Gail Browning		Fluvanna	Jennifer Coffey
35						Staunton	No Abstractor on File		Franklin	Gail Browning
36						Suffolk	Taylor Abstract, Droll's Express, Suffolk Title		Frederick	Independence Title
37						Virginia Beach	Droll's Express		Giles	No Abstractor on File
38						Waynesboro	No Abstractor on File		Gloucester	Droll's Express
39						Williamsburg	Taylor Abstract, Droll's Express		Goochland	Powhatan Title, Greater Richmond Abstract
40						Winchester	Independence Title, Jefferson Title		Grayson	No Abstractor on File
41									Greene	Jennifer Coffey
42									Greensville	No Abstractor on File
43									Halifax	Trinity Title
44									Hanover	Powhatan Title, Terry's Title and Abstract, Greater Richmond Abstract
45									Henrico	Powhatan Title, Greater Richmond Abstract
46									Henry	No Abstractor on File
47									Highland	No Abstractor on File
48									Isle of Wight	Taylor Abstract, Droll's Express, Suffolk Title
49									James City	Taylor Abstract, Droll's Express, Greater Richmond Abstract, Eastern Virginia Title
50									King and Queen	Greater Richmond Abstract
51									King George	Terry's Title and Abstract
52									King William	Powhatan Title, Greater Richmond Abstract
53									Lancaster	No Abstractor on File
54									Lee	No Abstractor on File
55									Loudoun	Independence Title, Jefferson Title
56									Louisa	Powhatan Title, Terry's Title and Abstract, Jennifer Coffey
57									Lunenburg	No Abstractor on File
58									Madison	Terry's Title and Abstract, Jennifer Coffey
59									Mathews	Droll's Express
60									Mecklenburg	No Abstractor on File
61									Middlesex	No Abstractor on File
62									Montgomery	No Abstractor on File
63									Nelson	Jennifer Coffey
64									New Kent	Powhatan Title, Droll's Express, Greater Richmond Abstract
65									Norfolk	Droll's Express
66									Northampton	Droll's Express, Shore Title, LLC
67									Northumberland	No Abstractor on File
68									Nottoway	No Abstractor on File
69									Orange	Terry's Title and Abstract, Jennifer Coffey
70									Page	No Abstractor on File
71									Patrick	No Abstractor on File
72									Pittsylvania	Trinity Title
73									Powhatan	Powhatan Title, Greater Richmond Abstract
74									Prince Edward	No Abstractor on File
75									Prince George	Powhatan Title, Greater Richmond Abstract
76									Prince William	Independence Title, Jefferson Title
77									Pulaski	No Abstractor on File
78									Rappahannock	No Abstractor on File
79									Richmond	Terry's Title and Abstract
80									Roanoke	Trinity Title, Suffolk Title, Gail Browning
81									Rockbridge	Trinity Title
82									Rockingham	No Abstractor on File
83									Russell	No Abstractor on File
84									Scott	No Abstractor on File
85									Shenandoah	No Abstractor on File
86									Smyth	No Abstractor on File
87									Southampton	Droll's Express, Suffolk Title
88									Spotsylvania	Independence Title, Terry's Title and Abstract
89									Stafford	Independence Title, Jefferson Title, Terry's Title and Abstract
90									Surry	Droll's Express
91									Sussex	Droll's Express
92									Tazewell	No Abstractor on File
93									Warren	Independence Title
94									Washington	No Abstractor on File
95									Westmoreland	Terry's Title and Abstract
96									Wise	No Abstractor on File
97									Wythe	No Abstractor on File
98									York	Taylor Abstract, Droll's Express
Sheet3

Cell Formulas
Range	Formula
E5	E5	=IFERROR(IF(D5="",M2,INDEX($M$2:$M$98,MATCH(D5,$L$2:$L$98,0))),M2)
E13	E13	=IFERROR(IF(D13="",J2,INDEX($J$2:$J$40,MATCH(D13,$I$2:$I$40,0))),J2)

Cells with Data Validation
Cell	Allow	Criteria
D5	List	=$L$2:$L$98
D13	List	=$I$2:$I$40

 

[Harbor1704]

Thank you so much, it never occurred to me to use Index!

 

[hajiali]

Gald that work and that I could help.