Make Vlookup More Easy and Simple

[muhammad susanto]

hello all..

i have excel formula, that's make confused :
how to make more easy or similar that, name sheet "Input Lap"

=VLOOKUP(VLOOKUP($N$2;'Input Lap'!$A:$CQ;ROW([COLOR=#ff0000]A23[/COLOR]);FALSE);'Input Lap'!$A:$CQ;69;FALSE)

i must typing one by one to change cell A23. A24,.....etc....
any help thank in advance...

m.susanto

 

[JackDanIce]

Change ROW(23) to ROW()

You may need to adjust i.e. try ROW()+1 or ROW()-1 etc

 

[muhammad susanto]

ok Jack....how to make more simple vlookup syntax
it's possible.?

 

[JackDanIce]

What is the value in N2 and what is the table in Input Map? Can you explain, using 2 different examples, what you want the formula to do?

 

[muhammad susanto]

What is the value in N2 and what is the table in Input Map? Can you explain, using 2 different examples, what you want the formula to do?

thanks this Jack :

Excel 2007
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
2									Row			1
3
111	No	Property										Code Items								Amount
112	1	Rumah Negara Golongan I Tipe C Permanen										4010201007 - 29								961.002.246.483.15
113	2	Rumah Negara Golongan I Tipe C Permanen										4010201007 - 30								1.087.268.732.928.00
114	2	#N/A										#N/A								-
115	2	#N/A										#N/A								-
116	2	#N/A										#N/A								-
LAP REVAL

 

[muhammad susanto]

this my file attachment :
https://app.box.com/s/ub91758o1cig1alylldmjn8gs0k0fffx

my problem in column Property and column code items -- i must typing one by one to change cell A23. A24,.....etc....

 

[muhammad susanto]

i have use ROW()+1 or ROW()-1 but stilt not working

 

[Aladin Akyurek]

When this formula of yours...

=VLOOKUP(VLOOKUP($N$2;'Input Lap'!$A:$CQ;ROW(A23);FALSE);'Input Lap'!$A:$CQ;69;FALSE)

will be looking like this successively:

=VLOOKUP(VLOOKUP($N$2;'Input Lap'!$A:$CQ;{23};FALSE);'Input Lap'!$A:$CQ;69;FALSE)

=VLOOKUP(VLOOKUP($N$2;'Input Lap'!$A:$CQ;{24};FALSE);'Input Lap'!$A:$CQ;69;FALSE)

=VLOOKUP(VLOOKUP($N$2;'Input Lap'!$A:$CQ;{25};FALSE);'Input Lap'!$A:$CQ;69;FALSE)

Etc...

So what is the real problem?

 

[muhammad susanto]

thanks Aladin..

i want copy down that formula but not working :
=VLOOKUP(VLOOKUP($N$2;'Input Lap'!$A:$CQ;{23};FALSE);'Input Lap'!$A:$CQ;69;FALSE)
i always change reference cell 23,24,25, etc....very manually...

when i copy down formula the cell not change still in {23}

 

[Aladin Akyurek]

thanks Aladin..

i want copy down that formula but not working :
=VLOOKUP(VLOOKUP($N$2;'Input Lap'!$A:$CQ;{23};FALSE);'Input Lap'!$A:$CQ;69;FALSE)
i always change reference cell 23,24,25, etc....very manually...

when i copy down formula the cell not change still in {23}

are you not wanting that

ROW(A23) gives 23

and copied down, it becomes 24, 25, etc?