Lookup
 

Good afternoon,

In a worksheet I have five columns.

Zip Code II Zip Code I OT Carrier Transit Date
64429 50010 1 DHRN 1 11/15/2006
64429 50010 8 DHRN 1 11/15/2006
64429 50010 7 DHRN 1 11/15/2006
64429 50010 2 DHRN 1 11/15/2006
64429 50010 4 DHRN 1 11/15/2006
64429 50014 1 DHRN 1 11/15/2006
64429 50014 8 DHRN 1 11/15/2006
64429 50014 7 DHRN 1 11/15/2006


I would like to create a second worksheet to use as a cross table of the
data in the first worksheet. I would like to have the carrier name put at
the intersection of the column (OT) and the row Zip Code I.

So if in the new table I have:
- A2 = 50010
- B1 = 1
- Then b2= DHRN

What would be the formula that would take the data from an organized list
and create a cross table. I tried Match / Index but I do not get the result.

Thanks

Lookup
 

Perhaps something like this:

With
Your posted table on Sheet2, cells A1:F9

Then, on Sheet1
A2: (a Zip Code I value)
B1: (an OT value)

B2:
=INDEX(Sheet2!$D$2:$D$9,SUMPRODUCT((Sheet2!$B$2:$B $9&"_"&Sheet2!$C$2:$C$9=A2&"_"&B1)*ROW(Sheet2!$B$2 :$B$9))-ROW(Sheet2!$B$1),1)

OR...this ARRAY FORMULA
B2:
=INDEX(Sheet2!D2:D9,MATCH(Sheet1!A2&"_"&Sheet1!B1, Sheet2!$B$2:$B$9&"_"&Sheet2!$C$2:$C$9,0),1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"MIchel Khennafi" wrote:

Good afternoon,

In a worksheet I have five columns.

Zip Code II Zip Code I OT Carrier Transit Date
64429 50010 1 DHRN 1 11/15/2006
64429 50010 8 DHRN 1 11/15/2006
64429 50010 7 DHRN 1 11/15/2006
64429 50010 2 DHRN 1 11/15/2006
64429 50010 4 DHRN 1 11/15/2006
64429 50014 1 DHRN 1 11/15/2006
64429 50014 8 DHRN 1 11/15/2006
64429 50014 7 DHRN 1 11/15/2006


I would like to create a second worksheet to use as a cross table of the
data in the first worksheet. I would like to have the carrier name put at
the intersection of the column (OT) and the row Zip Code I.

So if in the new table I have:
- A2 = 50010
- B1 = 1
- Then b2= DHRN

What would be the formula that would take the data from an organized list
and create a cross table. I tried Match / Index but I do not get the result.

Thanks

Lookup
 

Another option....using DGET()

With
Your posted table on Sheet2, cells A1:F9

Then...on Sheet1
A1: Zip Code I
A2: (Zip Code I value)
B1: OT
B2: (an OT value)

C1: Carrier
C2: =DGET(Sheet2!$A$1:$F$9,"CARRIER",A1:B2)

Example of Results:
Zip Code I OT Carrier
50010 2 DHRN

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MIchel Khennafi" wrote:

Good afternoon,

In a worksheet I have five columns.

Zip Code II Zip Code I OT Carrier Transit Date
64429 50010 1 DHRN 1 11/15/2006
64429 50010 8 DHRN 1 11/15/2006
64429 50010 7 DHRN 1 11/15/2006
64429 50010 2 DHRN 1 11/15/2006
64429 50010 4 DHRN 1 11/15/2006
64429 50014 1 DHRN 1 11/15/2006
64429 50014 8 DHRN 1 11/15/2006
64429 50014 7 DHRN 1 11/15/2006


I would like to create a second worksheet to use as a cross table of the
data in the first worksheet. I would like to have the carrier name put at
the intersection of the column (OT) and the row Zip Code I.

So if in the new table I have:
- A2 = 50010
- B1 = 1
- Then b2= DHRN

What would be the formula that would take the data from an organized list
and create a cross table. I tried Match / Index but I do not get the result.

Thanks