Vlookup: unexplainable - for me - #N/A

[Hans Troost]

I'm completely stuck: just missing - checking and cheking again - what I do wrong with this VLookup.

1. Got it working in this example:

Validate the value for B10 with list =$F$2:$F$7 and with vlookup in A6 get the corresponding value from column G. Works perfect.
Formula in A6: =VLOOKUP(B10;F2:G7;2;FALSE)

Functioning one:

Pas G&O.xltx
	A	B	C	D	E	F	G
1	Klantnummer:	01/250
2	Naam:	Dhr. A. van Lekkerkerker				Baarn	06-38496812
3						Blaricum en Laren	06-38547751
4	Afmelden:					Hilversum	06-46454235
5	VÓÓR 10:30 uur op de uitgiftedag					Huizen	06-38496811
6	06-38547751	/ 035-5338836				Soest	06-3849680
7						Weesp	12345678
8
9
10	Uitgiftepunt:	Blaricum en Laren
Sheet1

Cell Formulas
Range	Formula
A6	A6	=VLOOKUP(B10,F2:G7,2,FALSE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A7	Cell Value	=$A$7	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A6	List	=$G$2:$G$7
B10	List	=$F$2:$F$7

2: Not working:
Validate column D values with list =$G$2:$G$8 and want to get the corresponding mobile number from column F.
Formula in C2 and further down: =VLOOKUP(D2;$F$2:$G$8;1;FALSE) delivers #N/A

Nieuwe klanten.xlsx
	A	B	C	D	E	F	G
1	Klantnummer	Naam	Mobiel	Uitgiftepunt		Mobiel	Uitgiftepunt
2	01/1912	Klant01	06-46454235	02 Hilversum vrijdag		06-38496811	00 Huizen
3	01/3288	Klant02	#N/A	01 Hilversum donderdag		06-46454235	01 Hilversum donderdag
4	00/3230	Klant03	#N/A	00 Huizen		06-46454235	02 Hilversum vrijdag
5	00/961	Klant04	#N/A	00 Huizen		06-3849680	03 Soest
6	03/2541	Klant05	#N/A	03 Soest		06-38496812	04 Baarn
7	02/3254	Klant06	#N/A	02 Hilversum vrijdag		06-1234567	05 Weesp
8	03/674	Klant07	#N/A	03 Soest		06-38547751	08 BEL (Blaricum/Eemnes/Laren)
9	05/2248	Klant08	#N/A	05 Weesp
10	01/3228	Klant09	#N/A	01 Hilversum donderdag
11	03/697	Klant10	#N/A	02 Hilversum vrijdag
12	08/2511	Klant11	#N/A	03 Soest
13	01/3297	Klant12	#N/A	01 Hilversum donderdag
14	02/2343	Klant13	#N/A	02 Hilversum vrijdag
Nieuwe klanten

Cell Formulas
Range	Formula
C2	C2	='D:\Documents\4DD\Klantenpassen\Visitekaartjes\[Pas G&O2.xlsx]Sheet1'!$A$6
C3:C14	C3	=VLOOKUP(D3,$F$2:$G$8,1,FALSE)

Cells with Data Validation
Cell	Allow	Criteria
G2:G3	List	=$G$2:$G$8
D1:D14	List	=$G$2:$G$8

What do I do wrong in this 2nd case?

Any help appreciated,

Hans Troost

 

[hagia_sofia]

Hello, VLOOKUP does not work from right to left without some additional inputs (e.g. switching the columns via a formula). With 365 you could try e.g. XLOOKUP:

=XLOOKUP(D2:D14,G2:G8,F2:F8)

 

[RoryA]

VLOOKUP will only look for the lookup value in the first column of the table. Either use INDEX and MATCH or XLOOKUP if you are using a version that supports it:

=XLOOKUP(D3,$G$2:$G$8,$F$2:$F$8)

 

[Hans Troost]

Many thanks!!

Just manually switched columns F and G and problem solved! I did not know that "VLOOKUP will only look for the lookup value in the first column of the table". This was my 1st experiendce with VLOOKUP.

So supportive and fast!

Have a nice day - I now have...

Hans Troost