-1

On K250 cell I've this put a formula so that when user submit data via form formula will work.

=VLOOKUP(F250,Available!$C$1:$E$72,3,false))

But problem is when someone submit a form, row K250 is getting down as K251 & submitted form taking palce K250. I've found some other guys are talking about using Array. I've tried this one but didn't work.

=arrayformula(VLOOKUP(F250,Available!$C$1:$E$72,3,false))

Whats the solution?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Tanzil
  • 1
  • 1
  • 1
  • 3
  • 1
    `But problem is when someone submit a form, row K250 is getting down as K251 & submitted form taking palce K250.` I tried re-reading this many times but I am afraid, I don't understand what do you mean – Siddharth Rout Nov 29 '13 at 11:31

2 Answers2

1

If i understood your problem correctly then do this:

Instead of giving a fixed range , use name ranges to avoid this problem.

To access name ranges you can use F3 key while typing vlookup.

Following are some screenshots to help you out.

It should work even your cells shift towards down, if the shifting is happening towards right then you might want to select the entire sheet to avoid confusion.

enter image description here

enter image description here

enter image description here

Hope this helps to solve your problem.

PKumar
  • 10,971
  • 6
  • 37
  • 52
0

When a form submission is made in Google Sheets, a new row is inserted in the sheet receiving the form submissions, and yes, this will "push down" any formulae that were previously in that row.

And yes, one solution is to use an array formula. Something like this could be entered in row 1:

=ArrayFormula(IF(ROW(F:F)=1;"Column Header";IFERROR(VLOOKUP(F:F;Available!$C$1:$E$72;3*SIGN(ROW(F:F));0)))

Multivalue Parallel Lookup Solution

Note: if this answer is in the right ballpark, I will try and edit your question and tags accordingly.

AdamL
  • 23,691
  • 6
  • 68
  • 59