how to lookup a value for a value

[chads_vstream]

I need to pull info from sheet 2 to a table to sheet

sheet1
b
1 2 3
Qty
Size
Type


sheet2
variable1 variable2 Qty Size Type
a 1 2 1 trc
a 2 4 1.5 cb
a 3 3 4 cpb
b 1 5 0.5 ckb
b 2 3 0.75 cb
b 3 4 2 trc

I need the result to return the values for the qty, size, type
for variable 2
for variable 1

b <-(variable)
1 2 3
Qty (5) (3) (4)
Size (.5) (.75) (2)
Type (ckb) (cb) (trc)

any help would be appreciated.

 

[Max]

One way ..

Source table as posted is assumed in Sheet2's A1:E7

In Sheet1,
Input the var1 value in A1, eg: b
In B1 across are listed the values for var2, viz.: 1,2,3
In A2 down are listed the col headers: Qty, Size, Type

Put in B2, array-enter (press CTRL+SHIFT+ENTER to confirm the formula)
=INDEX(OFFSET(Sheet2!$A$2:$A$7,,MATCH($A2,Sheet2!$1:$1,0)-1),MATCH(1,(Sheet2!$A$2:$A$7=$A$1)*(Sheet2!$B$2:$B$7=B$1),0))
Copy B2 across/fill down to D4 populate
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik

 

[Roger Govier]

Hi Max

You can avoid the volatile Offset call, with this non-array entered formula

=INDEX(Sheet2!$A$1:$E$7,
MATCH(Sheet1!$A$1,Sheet2!$A$1:$A$7,0)+B$1-1,
MATCH(Sheet1!$A2,Sheet2!$A$1:$E$1,0))

Equally, should the source data not always be in numeric order of 1,2,3 for
Variable 2, the result will still be as the OP required

 

[chads_vstream]

Roger this works, kinda.
i'm getting extra data. Ex. I'm getting b data for lines where the a data
ends. I need the formula to only look at the specified serial # and then
pull the line numbers that apply into the form, and leave blank the line#'s
that have no data in them. Any clues as to how to get this to work.

This is what I'm getting

A 1 2 3 4 5
qty 2 4 3 5 0
size 1 1.5 4 0.5 0
type TRC CB CPB CKB 0
^
there is
no line 4 for SN 'A'



SERIAL # LINE# QTY SIZE TYPE
A 1 2 1 TRC
A 2 4 1.5 CB
A 3 3 4 CPB
B 1 5 0.5 CKB
B 2 3 0.75 CB
B 3 4 2 TRC
C 1 10 3 CPB

Thanks for your help
Chad