Vlookup where range changes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that I need to pull data from each week. The report has the
same four sales categories, just pulled by different filters.

For example:
Filter 1
Category 1
Category 2
Category 3
Category 4

Filter 2
Category 1
Category 2
Category 3
Category 4

Filter 3
Category 1
Category 2
Category 3
Category 4

I need to pull data from Filter 2, Category 1. The problem is that the
position of the rows change each week. The columns stay the same.

I have used a Match function to tell me the row for Filter 2, but I need to
use that in conjunction with a Vlookup to pull data from the columns next to
Category 1. Any ideas on how to do this?
 
Add headers to your data and remove spaces to get this:
Bin Lot
Filter1
Category1 11
Category2 12
Category3 13
Category4 14

Filter2
Category1 21
Category2 22
Category3 23
Category4 24

Filter3
Category1 31
Category2 32
Category3 33
Category4 34

Filt
2
Caty
1
Result
21

Select the array from Bin to 34 and
Insert > Name > Create > Top Row
Name Filt and Caty in the same way.
The 2 and 1 are the desired Filter and Category numbers.
Enter this formula in Results:
=INDEX(Lot,MATCH("Filter"&Filt,Bin,0)+Caty)
 
Back
Top