Excel Vlookup visible (filtered) data
Why Stack Overflow recommends it
Stack Overflow users link it when explaining that VLOOKUP ignores filtering by default and that a SUBTOTAL-based approach can restrict lookups to visible data.
Recommended by 1 people across 1 different months, 2017 to 2022.
What it is
A short screen-capture tutorial from the Excel Situation channel showing how to make VLOOKUP work on only the visible rows of a filtered range. It demonstrates combining SUBTOTAL with the lookup so hidden rows are ignored.
What people said
Recommends radiocontrolled · 2018-04 · Stack Overflow · on “How to Lookup a Filtered List”
VLOOKUP by itself does not respect filtering because table filtering is purely visual. You can use a SUBTOTAL within a VLOOKUP to perform a VLOOKUP on visible data: [this video]
Cites LoHer · 2017-05 · Stack Overflow · on “how to apply vlookup formula on filtered data/visible cells only”
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 [LINK] 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 │ ├ ─
Cites simple-solution · 2022-02 · Stack Overflow · on “VLOOKUP Macro for AutoFiltered data”
Working with filtered data is possible with array formulas as shown here [LINK] , here [this video] and here [LINK] . Why don't you copy the filtered data to a new Worksheet? And work with vlookup on the filtered data in the 2nd Worksheet? Sample Data: Wikipedia => List_of_countries_by_population [LINK] [LINK] Sub FilterTable_and_Copy() 'Prepare Sheet2 If Sheets(2).Name <> "Filtered Data" Then Sheets.Add After:=Sheets(1) Sheets(2).Name = "Filtered Data" End If Sheets(2).Columns("A:G").ClearContents 'The Data is prepared in the Table "myTable" 'ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$100"), , _ ' xlYes).Name = "myTable" 'Filter Data Sheets(1).Select ActiveSheet.Range("myTable").Auto