Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using INDEX & MATCH in same formula
It sounds like Vlookup may be what you need. If, for example, the material
name is in column A on the other sheets (Material Unit & Labor Unit), formulas like these on the Paint Schedule sheet should work: To get the material unit price: =IF(ISERROR(VLOOKUP(B2,'Material Unit $ By Substrate'!A:G,7,FALSE)),0,VLOOKUP(B2,'Material Unit $ By Substrate'!A:G,7,FALSE)) To get the labor unit price: =IF(ISERROR(VLOOKUP(B2,'Labor Unit $ By Substrate'!A:E,5,FALSE)),0,VLOOKUP(B2,'Labor Unit $ By Substrate'!A:E,5,FALSE)) In these examples, B2 on the Paint Schedule sheet has the material name to use (the dropdown selection). Hope this helps, Hutch "Lolamama" wrote: I am trying to reference information from an Excel database in a new worksheet. In the new worksheet (called 'Paint Schedule'), when I fill in the name of the material from a dropdown menu (in column B), I would like it to automatically fill in the material unit price (from column G in worksheet named 'Material Unit $ By Substrate') in column C of 'Paint Schedule,' as well as the labor unit price (from column E in worksheet named 'Labor Unit $ By Substrate') in column D of 'Paint Schedule.' The material name should correspond throughout. I **think** I need to use an INDEX & MATCH formula to accomplish this (based on other postings I've read), but I'm not sure how the two functions interrelate or how exactly to write the formula. Any help would be greatly appreciated. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using INDEX & MATCH in same formula
Genius, absolute genius! I NEVER would have figured that out on my own.
Whatever they're paying you, it's not enough:) "Tom Hutchins" wrote: It sounds like Vlookup may be what you need. If, for example, the material name is in column A on the other sheets (Material Unit & Labor Unit), formulas like these on the Paint Schedule sheet should work: To get the material unit price: =IF(ISERROR(VLOOKUP(B2,'Material Unit $ By Substrate'!A:G,7,FALSE)),0,VLOOKUP(B2,'Material Unit $ By Substrate'!A:G,7,FALSE)) To get the labor unit price: =IF(ISERROR(VLOOKUP(B2,'Labor Unit $ By Substrate'!A:E,5,FALSE)),0,VLOOKUP(B2,'Labor Unit $ By Substrate'!A:E,5,FALSE)) In these examples, B2 on the Paint Schedule sheet has the material name to use (the dropdown selection). Hope this helps, Hutch "Lolamama" wrote: I am trying to reference information from an Excel database in a new worksheet. In the new worksheet (called 'Paint Schedule'), when I fill in the name of the material from a dropdown menu (in column B), I would like it to automatically fill in the material unit price (from column G in worksheet named 'Material Unit $ By Substrate') in column C of 'Paint Schedule,' as well as the labor unit price (from column E in worksheet named 'Labor Unit $ By Substrate') in column D of 'Paint Schedule.' The material name should correspond throughout. I **think** I need to use an INDEX & MATCH formula to accomplish this (based on other postings I've read), but I'm not sure how the two functions interrelate or how exactly to write the formula. Any help would be greatly appreciated. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using INDEX & MATCH in same formula
This is a great resource for your future reference:
http://www.contextures.com/xlFunctions03.html Regards, Ryan-- -- RyGuy "Lolamama" wrote: Genius, absolute genius! I NEVER would have figured that out on my own. Whatever they're paying you, it's not enough:) "Tom Hutchins" wrote: It sounds like Vlookup may be what you need. If, for example, the material name is in column A on the other sheets (Material Unit & Labor Unit), formulas like these on the Paint Schedule sheet should work: To get the material unit price: =IF(ISERROR(VLOOKUP(B2,'Material Unit $ By Substrate'!A:G,7,FALSE)),0,VLOOKUP(B2,'Material Unit $ By Substrate'!A:G,7,FALSE)) To get the labor unit price: =IF(ISERROR(VLOOKUP(B2,'Labor Unit $ By Substrate'!A:E,5,FALSE)),0,VLOOKUP(B2,'Labor Unit $ By Substrate'!A:E,5,FALSE)) In these examples, B2 on the Paint Schedule sheet has the material name to use (the dropdown selection). Hope this helps, Hutch "Lolamama" wrote: I am trying to reference information from an Excel database in a new worksheet. In the new worksheet (called 'Paint Schedule'), when I fill in the name of the material from a dropdown menu (in column B), I would like it to automatically fill in the material unit price (from column G in worksheet named 'Material Unit $ By Substrate') in column C of 'Paint Schedule,' as well as the labor unit price (from column E in worksheet named 'Labor Unit $ By Substrate') in column D of 'Paint Schedule.' The material name should correspond throughout. I **think** I need to use an INDEX & MATCH formula to accomplish this (based on other postings I've read), but I'm not sure how the two functions interrelate or how exactly to write the formula. Any help would be greatly appreciated. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is this an Index/Match formula? | Excel Worksheet Functions | |||
Please help on Index/Match Formula | Excel Discussion (Misc queries) | |||
Need Help w/ Index and Match Formula | Excel Worksheet Functions | |||
Index/ Match Formula | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions |