Get data for 1 spreadsheet from another spreadsheet automatically in Excel 97?

But there’s a hitch – The info on spreadsheet B is not always inside same spot (always while in the same column but not similar line)

Case:Spreadsheet B (Source with data)

THE B C D E
Shiny 1000 2000 3000 Red
Dave FOUR THOUSAND 5000 6000 Blue
Mark 7000 8000 9000 Green

Spreadsheet ANY needs the images from Spreadsheet B (where filters are utilized to choose different organizations of persons i.e blue group, red staff & there can be several number of people in the list upon s.B)

The main problem for us is navigating around that 1 or over people is probably not on the list therefore , the data seriously isn’t always around the same brand so a super easy =sum(a2) or perhaps whatever fails.

Sheet ONE
Cellular A1 type in name, B1 enter colour, C1 =INDEX(Sheet2! B:B, MATCH(1, Sheet2! $F:$F, 0)) content across COUPLE OF cells

Sheet 2
Near to your variety enter F1 =IF(AND(A1=Sheet1! $A$1, E1=Sheet1! $B$1), ONE, 0) content down variety.