Creating lookup formulas for material cost spreadsheet

[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

[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


"mn_tater" wrote:

> 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

[mn_tater]

Thank you - That definalty helps me out!!
"Ron Coderre" wrote:

> 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
>
>
> "mn_tater" wrote:
>
> > 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

[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


On Thu, 15 Dec 2005 09:51:03 -0800, "Ron Coderre"
<[email protected]> wrote:

>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
>
>
>"mn_tater" wrote:
>
>> 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

[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


"Gord Dibben" wrote:

> 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
>
>
> On Thu, 15 Dec 2005 09:51:03 -0800, "Ron Coderre"
> <[email protected]> wrote:
>
> >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
> >
> >
> >"mn_tater" wrote:
> >
> >> 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
>

[Gord Dibben]

You know what I like about all the Excel groups.

Pretty much Everything!


Gord

On Thu, 15 Dec 2005 12:52:02 -0800, "Ron Coderre"
<[email protected]> wrote:

>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
>
>
>"Gord Dibben" wrote:
>
>> 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
>>
>>
>> On Thu, 15 Dec 2005 09:51:03 -0800, "Ron Coderre"
>> <[email protected]> wrote:
>>
>> >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
>> >
>> >
>> >"mn_tater" wrote:
>> >
>> >> 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
>>