# Modify a Macro to Continue LOOKUP Search

#### meppwc

##### Well-known Member
The macro below populates cells R4 and S4 of the “Summary” worksheet with data found on the “Lookup Tables” worksheet.
On “Summary” worksheet cell R4, lookup value in “Summary” cell O4 using lookup table on worksheet “Lookup Tables” cells A1 – A27 and once value is found return the value in that row found in column C. Here is what the formula would look like in cell R4 of the “Summary” worksheet: =LOOKUP(O4,'Lookup Tables'!\$A\$1:\$A\$27,'Lookup Tables'!\$C\$1:\$C\$27)

Then on “Summary” worksheet cell S4, lookup value in” Summary” cell O4 using lookup table on worksheet “Lookup Tables” cells A1 – A27 and once value is found return the value in that row found in column B. Here is what the formula would look like in cell S4 of the “Summary” worksheet: =LOOKUP(O4,'Lookup Tables'!\$A\$1:\$A\$27,'Lookup Tables'!\$B\$1:\$B\$27)

This macro works correctly for cells R4 and S4 of the “Summary” worksheet. But now I want the macro to continue doing lookups down to cells R50 and S50 of the “Summary” worksheet. If no value is present in column O of the “Summary” worksheet, then the lookup does not take place for that row.

Sub populatePOC()
'
' populatePOC Macro
'

'
Sheets("Summary").Select
Range("R4").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(RC[-3],'Lookup Tables'!R[-3]C[-17]:R[23]C[-17],'Lookup Tables'!R[-3]C[-15]:R[23]C[-15])"
Range("S4").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(RC[-4],'Lookup Tables'!R[-3]C[-18]:R[23]C[-18],'Lookup Tables'!R[-3]C[-17]:R[23]C[-17])"
Range("S5").Select
End Sub

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Replies
0
Views
248
Replies
3
Views
155
Replies
0
Views
298
Replies
1
Views
915
Replies
1
Views
199

1,191,587
Messages
5,987,512
Members
440,098
Latest member
MickyMouse123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back