Altering the point from which VLOOKUP begins searching

Hi everyone,

Worksheet 1 contains data as such:

Column A (BRAND):

AAA
BBB
CCC
DDD
AAA
DDD
BBB


Column B (FACTORY):

EEEE
FFFF
GGGG
HHHH
IIII
JJJJ
KKKK

Essentially, multiple, unique factory names are associated with one of five brands on Worksheet 1.

I would like to use VLOOKUP to have Worksheets 2 - 6 populate with data from Worksheet 1. Worksheet 2 will list factories producing brand AAA; Worksheet 3 will list factories producing brand BBB; Worksheet 4 will list factories producing brand CCC, etc.

The problem is that VLOOKUP begins searching at the top of the range in each iteration, so all cells populate with from the first found match.

Is there a way to structure the table_array such that VLOOKUP begins searching at the cell directly beneath the cell in which the last match was found?

Thanks and best regards,
ACurtis802