VLOOKUP is returning #N/A

[Sean15]

Hi:

I am using VLOOKUP to pull data from one list into another list but VLOOKUP returns #NA. When I manually copy and paste lookup value from one list into the other list, VLOOKUP returns required value. I have cleaned up both list using TRIM(CLEAN), but VLOOKUP still returns #NA. I have also used Power Query to transform lists, but VLOOKUP still returns #NA. Lookup values are the same on both list – I doubled checked it. Again, when I copy and paste lookout value from one list into the other list, VLOOKUP returns required value. What else can I do to remedy this?

Regards,
S

 

[alansidman]

Sean,
Upload a sample worksheet 8-10 records that is representative of your actual file. Let us try and resolve once we can see the issues. Hard to diagnose from what you have provided.

 

[Sean15]

Here is sample data:

List of album genre PQ - Copy.xlsx
	A	B					G	H
1	Album Artist	Album					Column7	Column8
2	3 Doors Down	Another 700 Miles					#N/A
3	3 Doors Down	Away from the Sun					Post grunge	album copied & paste from genre w/s to give required result
4	Creed	Human Clay					#N/A
5	Hoobastank	The Reason					#N/A
6	Lifehouse	Lifehouse					Post grunge	album copied & paste from genre w/s to give required result
7	Lifehouse	No Name Face					Post grunge	album copied & paste from genre w/s to give required result
8	Marcy Playground	Marcy Playground					Post grunge	album copied & paste from genre w/s to give required result
9	Matchbox Twenty	Yourself or Someone Like You					Post grunge	album copied & paste from genre w/s to give required result
10	Matchbox Twenty	More Than You Think You Are					#N/A
11	Third Eye Blind	Third Eye Blind					#N/A
12	The Calling	Camino Palmero					#N/A
MM albums

Cell Formulas
Range	Formula
G2:G12	G2	=VLOOKUP(A2&B2,CHOOSE({1,2},genre!$A$3:$A$52&genre!$B$3:$B$52,genre!$D$3:$D$52),2,FALSE)
Press CTRL+SHIFT+ENTER to enter array formulas.

And here:

List of album genre PQ - Copy.xlsx
	A	B		D
2	Band	Studio albums
3	3 Doors Down	The Better Life		Post grunge
4	3 Doors Down	Away from the Sun		Post grunge
5	3 Doors Down	Seventeen Days		Post grunge
6	3 Doors Down	3 Doors Down		Post grunge
7	3 Doors Down	Time of My Life		Post grunge
8	3 Doors Down	Us and the Night		Post grunge
9	The Calling	Camino Palmero		Post grunge
10	The Calling	Two		Post grunge
11	Creed	My Own Prison		Post grunge
12	Creed	Human Clay		Post grunge
13	Creed	Weathered		Post grunge
14	Creed	Full Circle		Post grunge
15	Hoobastank	Hoobastank		Post grunge
16	Hoobastank	The Reason		Post grunge
17	Hoobastank	Every Man For Himself		Post grunge
18	Hoobastank	Fornever		Post grunge
19	Hoobastank	Fight or Flight		Post grunge
20	Hoobastank	Push Pull		Post grunge
21	Lifehouse	No Name Face		Post grunge
22	Lifehouse	Stanley Climbfall		Post grunge
23	Lifehouse	Lifehouse		Post grunge
24	Lifehouse	Who We Are		Post grunge
25	Lifehouse	Smoke & Mirrors		Post grunge
26	Lifehouse	Almería		Post grunge
27	Lifehouse	Out of the Wasteland		Post grunge
28	Marcy Playground	Marcy Playground		Post grunge
29	Marcy Playground	Shapeshifter		Post grunge
30	Marcy Playground	MP3		Post grunge
31	Marcy Playground	Leaving Wonderland... in a fit of Rage		Post grunge
32	Marcy Playground	Lunch, Recess & Detention		Post grunge
33	Matchbox Twenty	Yourself or Someone Like You		Post grunge
34	Matchbox Twenty	Mad Season		Post grunge
35	Matchbox Twenty	More Than You Think You Are		Post grunge
36	Matchbox Twenty	North		Post grunge
37	Nickelback	Curb		Post grunge
38	Nickelback	The State		Post grunge
39	Nickelback	Silver Side Up		Post grunge
40	Nickelback	The Long Road		Post grunge
41	Nickelback	All the Right Reasons		Post grunge
42	Nickelback	Dark Horse		Post grunge
43	Nickelback	Here and Now		Post grunge
44	Nickelback	No Fixed Address		Post grunge
45	Nickelback	Feed the Machine		Post grunge
46	Third Eye Blind	Third Eye Blind		Post grunge
47	Third Eye Blind	Blue		Post grunge
48	Third Eye Blind	Out of the Vein		Post grunge
49	Third Eye Blind	Ursa Major		Post grunge
50	Third Eye Blind	Dopamine		Post grunge
51	Third Eye Blind	Screamer		Post grunge
52	Third Eye Blind	Our Bande Apart		Post grunge
genre

 

[Phuoc]

Try this:

=LOOKUP(2,1/(genre!$A$3:$A$52=A2)/(genre!$B$3:$B$52=B2),genre!$D$3:$D$52)

 

[Sean15]

@Phucos - still not getting expected results.

List of album genre PQ - Copy.xlsx
	A	B					G	H
1	Album Artist	Album					Column7	Column8
2	3 Doors Down	Another 700 Miles					#N/A
3	3 Doors Down	Away from the Sun					Post grunge
4	Creed	Human Clay					#N/A	Expected result - post grunge
5	Hoobastank	The Reason					#N/A	Expected result - post grunge
6	Lifehouse	Lifehouse					Post grunge
7	Lifehouse	No Name Face					Post grunge
8	Marcy Playground	Marcy Playground					Post grunge
9	Matchbox Twenty	Yourself or Someone Like You					Post grunge
10	Matchbox Twenty	More Than You Think You Are					#N/A
11	Third Eye Blind	Third Eye Blind					#N/A	Expected result - post grunge
12	The Calling	Camino Palmero					#N/A	Expected result - post grunge
MM albums

Cell Formulas
Range	Formula
G2:G12	G2	=LOOKUP(2,1/(genre!$A$3:$A$52=A2)/(genre!$B$3:$B$52=B2),genre!$D$3:$D$52)

 

[Sean15]

Hi:

I found solution to remove problematic characters and non-breaking space here: Remove leading and trailing spaces from text
formula: =TRIM(CLEAN(SUBSTITUTE(B3,CHAR(160)," "))) - applied to columns A and B of list 1 and list 2

Following clean up, formula in post #4 returns all required values.
Thank you Phuoc.

Regards,
S

​