Make Fomrula Shorter..

[Jhon Mustofa]

hi all...

how to make more shorter this formula below :

in L161----=VLOOKUP(VLOOKUP($N$2,'Input Lap'!$A:$CQ,(ROW()-161)/27+23,FALSE),'Input Lap'!$A:$CQ,9,FALSE)
in L188----=VLOOKUP(VLOOKUP($N$2,'Input Lap'!$A:$CQ,(ROW()-161)/27+23,FALSE),'Input Lap'!$A:$CQ,9,FALSE)
in L215----=VLOOKUP(VLOOKUP($N$2,'Input Lap'!$A:$CQ,(ROW()-161)/27+23,FALSE),'Input Lap'!$A:$CQ,9,FALSE)

any help, thanks in advance..

jhon m

[Glenn Kennedy]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AliGW]

Why do you want to shorten it? What's the problem? It isn't really very long.

[Jhon Mustofa]

in yellow shading with arrow as clue...

[Glenn Kennedy]

where did this bit come from?

(ROW()-161)/27+23

[Jhon Mustofa]

Why do you want to shorten it? What's the problem? It isn't really very long.

hi ali...i want more effective ..contains double vlookup & name sheets

[AlKey]

Here is a little bit shorter:
replace FALSE in your formula with zero.

Please Login or Register  to view this content.

[Jhon Mustofa]

i sheet "Input Lap"..col. I2 down (city Town)...that's data which i want to pull it...

[AliGW]

i want more effective

What is ineffective about it? What is it not doing properly?

[Jhon Mustofa]

Here is a little bit shorter:
replace FALSE in your formula with zero.

Please Login or Register  to view this content.

hi Alkey worked it...but i still confuse in myself...where come from +23

[AliGW]

where come from +23

You have had that explained here: https://www.excelforum.com/excel-for...3-int-row.html

It would be very much easier for everyone if these interrelated queries were kept in one thread.

[Glenn Kennedy]

I am rather confused.

But this seems to do what you want:

L161 =INDEX('Input Lap'!I2:$I$116,$N$2)

L188 =INDEX('Input Lap'!I2:$I$116,$N$2+1)

L215 =INDEX('Input Lap'!I2:$I$116,$N$2+2)

But some explanation of what you are hoping the formula should do would be helpful!!!!

[Jhon Mustofa]

hi Glen..it works...but your formula can't copied down..i must type manual / change range..
L161 =INDEX('Input Lap'!I2:$I$116,$N$2)

L188 =INDEX('Input Lap'!I2:$I$116,$N$2+1)

L215 =INDEX('Input Lap'!I2:$I$116,$N$2+2)

[63falcondude]

In response to post #13,

L161 =INDEX('Input Lap'!$I$2:$I$116,$N$2)+INT((ROWS($1:1)-1)/27)

[Glenn Kennedy]

L161 copied down:

=INDEX('Input Lap'!$I$2:$I$116,$N$2+INT((ROWS($1:1)-1)/27))

[Glenn Kennedy]

63FD.. you didn't spot my error with the lack of $$$$ in the first range!!!

[63falcondude]

63FD.. you didn't spot my error with the lack of $$$$ in the first range!!!

You are right, I did not! I didn't look at the formula at all to be honest.

[Jonmo1]

A couple expressions seem to fit this thread...

Shorter Does Not Mean Better.
If it isn't broken, don't fix it.

[Jhon Mustofa]

thank guys....you're great!!! I love this forum.....