how to lookup a value for a value

  • Thread starter Thread starter chads_vstream
  • Start date Start date
C

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.
 
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
 
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
 
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
 
Back
Top