vlookup with offset?
 

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?

vlookup with offset?
 

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

vlookup with offset?
 

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?

--
HTH,
Barb Reinhardt



"d0711" wrote:

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?

vlookup with offset?
 

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

"Jarek Kujawa" wrote:

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

vlookup with offset?
 

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.

--
Biff
Microsoft Excel MVP


"d0711" wrote in message
...
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

"Jarek Kujawa" wrote:

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

vlookup with offset?
 

would this help?

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

insert it into B2 of Sheet2 and copy down