Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating lookup formulas for material cost spreadsheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating lookup formulas for material cost spreadsheet
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating lookup formulas for material cost spreadsheet
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating lookup formulas for material cost spreadsheet
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" 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating lookup formulas for material cost spreadsheet
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" 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating lookup formulas for material cost spreadsheet
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" 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Formulas for overtime | Excel Worksheet Functions | |||
creating multiple files from a spreadsheet | Excel Discussion (Misc queries) | |||
creating spreadsheet with colunms for names adresses & zipcodes | Excel Discussion (Misc queries) | |||
Creating Spreadsheet Calculations | Excel Discussion (Misc queries) | |||
Creating a summary from existing spreadsheet data ... | Excel Worksheet Functions |