vlookup with offset?

[d0711]

I am having trouble trying bring data from sheet 1 layed out as follows:

A B C

PARTName01 Shipped 3
Not Receipted 1
Value Not Receipted $1,114.00
% Compliant 66.67%

There are mulitple and specific part namesgoing down sheet 1 in the same
manner. I amtrying to use a lookup table in sheet 2 that references the
specific partname and am trying to get the value of the "not receipted" in
column C.

Can anyone hwelp with this?

 

[Jarek Kujawa]

if you already have a list of yr parts then use the formula:

=OFFSET(INDIRECT("A"&MATCH(A4,$A$1:$A$200,)),1,2)

for "Not Receipted"

or

=OFFSET(INDIRECT("A"&MATCH(A4,$A$1:$A$200,)),2,2)

for "Value Not Receipted"


hih

 

[Barb Reinhardt]

What I'd do is make sure that you have part names in each cell in column A if
there is something in column B. If you want to "hide" the names, you could
put a conditional format on like this

Select Column A
Format -> Conditional Format
in the formula put
=COUNTIF(A$1:A1,A1) > 1

Change A1 to the first cell in column A you choose.
Format the font color to white.

OK, now to the VLOOKUP

I'd add a helper column that concatenates column A and Column B. To use
VLOOKUP, you'd need to insert it before column C.
D1: =A1 & " " & B1

If you put it in column D, on the sheet you're pulling the data to, put this

Let's say you're putting this

Sheet2!E1: =MATCH(Sheet2!A1 & " " & "not receipted",Sheet1!$D:$D,0)
Where A1 contains the part name
Sheet2!F1: = INDEX(Sheet1!$C:$C,Sheet2!E1)

Once you have it working, you can replace sheet2!e1 IN Column F with the
formula in cell E1.

Make sense?

 

[d0711]

I should explain better Sheet1 looks like this

A B C

PARTName01 Shipped 3
Not Receipted 1
Value Not Receipted $1,114.00
% Compliant 66.67%
PARTName02 Shipped 3
Not Receipted 1
Value Not Receipted $1,114.00
% Compliant 66.67%

I am trying to pull the Not Receipted value in to another sheet (sheet2) for
each Partname list on sheet1 ( of which there are hundreds). Sheet 2 looks
like this:

A B
PARTNAME NOT RECEIPTED
Partname01
partname02
partname03

Sorry - if this seems redundant but I have gotten myself very confused

 

[T. Valko]

Try this:

Entered on Sheet2 B2:

=INDEX(Sheet1!C$1:C$16,MATCH(A2,Sheet1!A$1:A$16,0)+2)

Copy down as needed.

 

[Jarek Kujawa]

would this help?

=OFFSET(INDIRECT("Sheet1!A"&MATCH(A4,$A$1:$A$200,)),1,2)

insert it into B2 of Sheet2 and copy down