how to apply vlookup formula on filtered data/visible cells only

Question

I have filtered column "A" and i need to apply vlookup formula on the visible cells in column "A". How do i achieve this in vba . number of total rows in the worksheet are 30,000 and the filtered rows are closed to 100.

Answer 1

You could solve this problem without VBA, just with an Excel array formula.
But if you wish to set this formula with VBA, just use the Range.FormulaArray property.

We are using this array formula:

{=VLOOKUP(A2,IF(SUBTOTAL(3,OFFSET(Table2!$B$2:$B$5,ROW(Table2!$B$2:$B$5)-ROW(Table2!$B$2),0,1)),Table2!$B$2:$C$5,0),2,0)}

To use it in Excel you need to paste it without the enclosing curly brackets and press ctrl + shift + enter (Windows) or cmd + shift + enter (Mac) to activate the array formula.

---

How does the formula work?

The data sheet Table2 looks like this:

  Column A    Column B    Column C
┌  ─  ─  ─  ┬  ─  ─  ─  ┬  ─  ─  ─  ┐
│   Month   │   Store   │  Revenue  │
├  ─  ─  ─  ┼  ─  ─  ─  ┼  ─  ─  ─  ┤
│    Jan    │     1     │   6.000   │
├  ─  ─  ─  ┼  ─  ─  ─  ┼  ─  ─  ─  ┤
│    Jan    │     2     │   8.000   │
├  ─  ─  ─  ┼  ─  ─  ─  ┼  ─  ─  ─  ┤
│    Feb    │     1     │  10.000   │
├  ─  ─  ─  ┼  ─  ─  ─  ┼  ─  ─  ─  ┤
│    Feb    │     2     │  12.000   │
└  ─  ─  ─  ┴  ─  ─  ─  ┴  ─  ─  ─  ┘

The data sheet is filtered by month, so that February is visible only.
The result will be look like this:

  Column A    Column B
┌  ─  ─  ─  ┬  ─  ─  ─  ┐
│   Store   │  Revenue  │
├  ─  ─  ─  ┼  ─  ─  ─  ┤
│     1     │   10.000  │ Formula: {=VLOOKUP(A2,IF(SUBTOTAL(3,OFFSET(Table2!$B$2:$B$5,ROW(Table2!$B$2:$B$5)-ROW(Table2!$B$2),0,1)),Table2!$B$2:$C$5,0),2,0)}
├  ─  ─  ─  ┼  ─  ─  ─  ┤
│     2     │   12.000  │ Formula: {=VLOOKUP(A3,IF(SUBTOTAL(3,OFFSET(Table2!$B$2:$B$5,ROW(Table2!$B$2:$B$5)-ROW(Table2!$B$2),0,1)),Table2!$B$2:$C$5,0),2,0)}
└  ─  ─  ─  ┴  ─  ─  ─  ┘

My answer is based on this solution.