index, match, vlookup, array ....combining all these?

Hi all,

I have a spreadsheet with 2 work sheets (see attached).

The first worksheet has the orginal data.

In the second worksheet I am trying to determine the department and size from the monthly data in row E to AC. ie matching the values in E:AC in both worksheets to give the result.

Example:
if row E2:AC2 in worksheet "calculated" is available in the array on worksheet "original" E2:AC6, display the result of value B2 and C2

Hoping this isn't too confusing, if you have a look at the spreadsheet I think you will understand what I am trying to do. Any questions let me know

THANKS
Hong

Hi,

Thank you for the reply.
I have trying to understand the formula and for some reason cant work out adjust it.

If I simply do this =INDEX(orginal!B1:B5,MATCH(E8,orginal!E1:E5,0),1) then it calculates and matches correct, however as soon as i add in the "&" for multiple selections the formula errors out....thoughts?
example
=INDEX(orginal!$B$2:$C$6,MATCH(calculated!$E5&calculated!$F5&calculated!$G5,orginal!$E$2:$E$6&orginal!$F$2:$F$6&orginal!$G$2:$G$6,0),1)

Thanks
H

hmm... to be honest I have not used CSE before. Is it simply pressing CTRL SHIFT ENTER instead of enter after you have entered in the formula above.
Stilll does not work?

Thanks
Hog

I think I got it. Just applying the formula across 6000 rows and taking a very long time.

Thank you very much for your help!