Which functions will do this?

[PointerMan]

I'm looking for a formula (or combination of several) that will help me
transform columns of repeated data into a horizontal format.

Here's my sample data set:

Task Area Job Task 10 20 30
A 1 10 A X
A 2 20 B
A 1 30 C
A 4 40 D
A 3 50 E
I want to be able to match up the task and the job to solve for "X".
Essentially, I want to look for the specified task (in this case it's "A")
and find the job that's called out in the row above it's location (in this
case it's "10"). I've tried using INDEX & MATCH but haven't been able to
figure it out.

 

[Shane Devenshire]

Hi,

Try
=SUMPRODUCT(--($A$2:$A$16=$D2),--($C$2:$C$16=E$1),$B$2:$B$16)

This assumes your data are in columna A:C starting with titles on row 1. It
also assumes that the first task is in D2 and the first job in E1 in your
output grid.