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

 

[muhammad susanto]

yes. i have do but show "the formula you typed contains error.."
please, help me out...

 

[Aladin Akyurek]

yes. i have do but show "the formula you typed contains error.."
please, help me out...

You file contains this in D112 of LAP REVAL...

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

which does not make Excel to spit out "the formula you typed contains error".

This bit looks up $N$2 in 'Input Lap'!$A:$CQ and returns a result from 23th column of 'Input Lap'!$A:$CQ, that is, from column W of Input Lap. This column has B1 as header.

The outer VLOOKUP in

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

looks up the result of the inner VLOOKUP in 'Input Lap'!$A:$CQ and returns a result from the 8th column of 'Input Lap'!$A:$CQ, that is, column H of Input Lap. This column has Objek Penilaian as header.

You can #N/A from the inner as well as the outer VLOOKUP.

In D114 you get #N/A when the inner VLOOKUP returns "" as result. This "" is not available in column A of 'Input Lap'!$A:$CQ, therefore the outer VLOOKUP returns #N/A.

Hope this helps.

 

[muhammad susanto]

hello aladin, thank you so much..
working well...

 

[muhammad susanto]

hi aladin, once again with the same problem :
different 16 rows

in row 167-----i use formula =Row(A23)
in row 183 --- i use formula =Row(A24)
in row 199 --- i use formula =Row(A25)

how make automatically row increase A23,A24,A25

 

[muhammad susanto]

how to adjust range using it :
=VLOOKUP(VLOOKUP($N$2;'Input Lap'!$A:$CQ,ROW(A23);FALSE);'Input Lap'!$A:$CQ;8;FALSE)

in row 167-----i use formula =Row(A23)
in row 183 --- i use formula =Row(A24)
in row 199 --- i use formula =Row(A25)

 

[muhammad susanto]

sorry i mistake :
how to adjust range using it :
=VLOOKUP(VLOOKUP($N$2;'Input Lap'!$A:$CQ,ROW(A23);FALSE);'Input Lap'!$A:$CQ;8;FALSE)

in row 167-----...row(A23)
in row 183 --- i want change to be ... (A24)
in row 199 --- i want change to be .... (A25)
​