Vlookup where range changes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg - 17 Mar 2006 15:52 GMT

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?

Reply to this Message

Herbert Seidenberg - 18 Mar 2006 00:41 GMT

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)

Reply to this Message