Lookup function

  • Thread starter Thread starter BAS
  • Start date Start date
B

BAS

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
 
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.
 
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.
 
Worked...

Thanks,
BAS

Sebastian said:
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.
Click to expand...
 
Back
Top