Google docs continue VLOOKUP with added rows

Question

In my google document column A is a list of requisition numbers, using VLOOKUP the title for each req. number is pulling in automatically as below, where the first title shows the VLOOKUP formula and the rest show the outcome (for the purposes of this example only).

      A             B
1  REQ NO.      REQ. Title
2  TP5443       =IF(A2="","",VLOOKUP(A2, 'TITLES'!F$3:G$25, 2,FALSE ))
3  TP5443       Warehouse
4  TP5443       Warehouse
5  TP5441       Leader
6  TP5441       Leader
7  TP5325       Driver
8  TP5325       Driver

This works fine, however if i wanted to add a new Warehouse line a new line is inserted beneath the last Warehouse entry, and I would like the VLOOKUP to auto complete in the new blank cell.

Google already recognises a new row is inserted and updates the remaining formulas accordingly.

      A             B
1  REQ NO.      REQ. Title
2  TP5443       Warehouse
3  TP5443       Warehouse
4  TP5443       Warehouse
5
6  TP5441       =IF(A6="","",VLOOKUP(A6, 'TITLES'!F$3:G$25, 2,FALSE ))
7  TP5441       Leader
8  TP5325       Driver
9  TP5325       Driver

So row 6 is automatically updated A6, instead of A5, however im left with B5 being blank.

How could I achieve this automatically?

Answer 1

In B2 try:

=ARRAYFORMULA(IF(LEN(A2:A), VLOOKUP(A2:A, 'TITLES'!F$3:G$25, 2,FALSE),))

Make sure all formulas below B2 are cleared. Then try to add a new row and see if this works ?