Lookup function
 

I have a worksheet "Spend Detail" with 8000+ part numbers, A7:A8253 and the
unit prices for each part # is in, G7:G8253 (Part # in A7 unit price is in G7
and so on).

I have now created another worksheet "ABC" but with only 120 or so part #'s
(that were filtered from Spend Detail) but didn't bring over the unit costs.
Is there a function to take the part #'s on sheet ABC and go and look for it
on sheet Spend Detail and bring over the corresponding unit cost?

It would take to much time to filter through and find the unit costs manually.

THanks,
BAS

Lookup function
 

Try this:

On the ABC sheet:

A1 = some part #

Enter this formula in B1:

=SUMIF('Spend Detail'!A$7:A$8253,A1,'Spend Detail'!G$7:G$8253)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"BAS" wrote in message
...
I have a worksheet "Spend Detail" with 8000+ part numbers, A7:A8253 and the
unit prices for each part # is in, G7:G8253 (Part # in A7 unit price is in
G7
and so on).

I have now created another worksheet "ABC" but with only 120 or so part
#'s
(that were filtered from Spend Detail) but didn't bring over the unit
costs.
Is there a function to take the part #'s on sheet ABC and go and look for
it
on sheet Spend Detail and bring over the corresponding unit cost?

It would take to much time to filter through and find the unit costs
manually.

THanks,
BAS

Lookup function
 

VLOOKUP will do that.

Source Table: (I'm naming the workseet "SOURCE" for illustration purposes).
A B C D E F
G
1 Part # Label Label Label Label Label
Unit Price
2 67595
$69.99
3 15359
$35.49
4 85695
$21.99
..
..
..
8253 12345
$11.99

Target Table
A B
1 Part # Unit Cost
2 67595 =VLOOKUP(A2,SOURCE!$A$1:$G$8253,7,FALSE)

You then copy that formula all the way down for the 120 cells that you have
on the target table and you'll get the unit prices that correspond to each
part number on it.

Hope this helps.




"BAS" wrote:

I have a worksheet "Spend Detail" with 8000+ part numbers, A7:A8253 and the
unit prices for each part # is in, G7:G8253 (Part # in A7 unit price is in G7
and so on).

I have now created another worksheet "ABC" but with only 120 or so part #'s
(that were filtered from Spend Detail) but didn't bring over the unit costs.
Is there a function to take the part #'s on sheet ABC and go and look for it
on sheet Spend Detail and bring over the corresponding unit cost?

It would take to much time to filter through and find the unit costs manually.

THanks,
BAS

Lookup function
 

Worked...

Thanks,
BAS

"Sebastian" wrote:

VLOOKUP will do that.

Source Table: (I'm naming the workseet "SOURCE" for illustration purposes).
A B C D E F
G
1 Part # Label Label Label Label Label
Unit Price
2 67595
$69.99
3 15359
$35.49
4 85695
$21.99
.
.
.
8253 12345
$11.99

Target Table
A B
1 Part # Unit Cost
2 67595 =VLOOKUP(A2,SOURCE!$A$1:$G$8253,7,FALSE)

You then copy that formula all the way down for the 120 cells that you have
on the target table and you'll get the unit prices that correspond to each
part number on it.

Hope this helps.




"BAS" wrote:

I have a worksheet "Spend Detail" with 8000+ part numbers, A7:A8253 and the
unit prices for each part # is in, G7:G8253 (Part # in A7 unit price is in G7
and so on).

I have now created another worksheet "ABC" but with only 120 or so part #'s
(that were filtered from Spend Detail) but didn't bring over the unit costs.
Is there a function to take the part #'s on sheet ABC and go and look for it
on sheet Spend Detail and bring over the corresponding unit cost?

It would take to much time to filter through and find the unit costs manually.

THanks,
BAS