Creating lookup formulas for material cost spreadsheet

M

mn_tater

I am trying to create a spread sheet that in one column material is either
entered or picked from a drop down list and depending on what is selected or
entered, the cost that corresponds to that particular material is displayed
 
R

Ron Coderre

Try this:

Beginning in Cell A1 on another sheet, I'll assume Sheet2, create a 2-col
list.
First column: PartNum
Second Column: Cost

Then, on sheet1....
A1: (some part number)
B1: =VLOOKUP(A1,Sheet2!A1:B100,2,0)

That formula will try to find the part number in Cell A1 in the first column
of the list on Sheet2. If it finds a match, it will return the corresponding
cost value.

Note: If you don't want errors to display for partnumbers that are not in
the list, use this version:
B1: =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B100,2,0)),"No
Match",VLOOKUP(A1,Sheet2!A1:B100,2,0))

Of course, adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron
 
M

mn_tater

Thank you - That definalty helps me out!!
Ron Coderre said:
Try this:

Beginning in Cell A1 on another sheet, I'll assume Sheet2, create a 2-col
list.
First column: PartNum
Second Column: Cost

Then, on sheet1....
A1: (some part number)
B1: =VLOOKUP(A1,Sheet2!A1:B100,2,0)

That formula will try to find the part number in Cell A1 in the first column
of the list on Sheet2. If it finds a match, it will return the corresponding
cost value.

Note: If you don't want errors to display for partnumbers that are not in
the list, use this version:
B1: =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B100,2,0)),"No
Match",VLOOKUP(A1,Sheet2!A1:B100,2,0))

Of course, adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron
Click to expand...
 
G

Gord Dibben

Ron

I just have to leap in here, albeit with some trepidation since your advice is
spot on in most cases.

The ISERROR function masks all errors.

The ISNA function might be a better function in a VLOOKUP formula.

I can't see how any other error except #N/A would arise in this particular
case but OP should not get in the habit of using the ISERROR for all formulas.

If OP had a formula like

=IF(ISERROR(VLOOKUP(D1,A1:B8,2,FALSE)/VLOOKUP(D2,A1:B8,2,FALSE)),"no
match",VLOOKUP(D1,A1:B8,2,FALSE)/VLOOKUP(D2,A1:B8,2,FALSE))

A value could be found and returned for each VLOOKUP statement but if value
returned for VLOOKUP(D2,A1:B8,2,FALSE) was zero or blank he could get a
#DIV/0! error which would be masked by the ISERROR and get "no match" even
though #N/A was not the error.


Gord Dibben Excel MVP
 
R

Ron Coderre

You know what I like about this forum?

I can never get away with being lazy in my advice.
Per usual, Gord, your comment is spot on. Thanks for giving me a newspaper
over the snout....I deserved it. :)

***********
Best Regards,
Ron
 
Top