Hello. Im a bit new to the INDEX and MATCH method and I'm really not sure if thats the best way, or if VLOOKUOP is better, or something else entirely.
I have a workbook with several sheets, the first is "invoice form", this is where the formula is located.
The rest of the sheets coincide with a different categories. These sheets start with 3 numbers such as "101 - PPE", "102 - Soil", and "103 - Oven".
I have the following example:
This is the "Invoice Form" Sheet
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]Part #[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD]101-PART1[/TD]
[TD="align: center"]Formula will go here[/TD]
[/TR]
[TR]
[TD]102-PART2[/TD]
[TD="align: center"]Formula will go here[/TD]
[/TR]
[TR]
[TD]103-PART3[/TD]
[TD="align: center"]Formula will go here[/TD]
[/TR]
[TR]
[TD]104-PART4[/TD]
[TD="align: center"]Formula will go here[/TD]
[/TR]
</tbody>[/TABLE]
This is the "101 - PPE" Sheet
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Part Description[/TD]
[/TR]
[TR]
[TD="align: center"]101-PART1[/TD]
[TD="align: center"]Leather Glove[/TD]
[/TR]
[TR]
[TD="align: center"]101-PART2[/TD]
[TD="align: center"]Hard Hat[/TD]
[/TR]
[TR]
[TD="align: center"]101-PART3[/TD]
[TD="align: center"]Safety Vest[/TD]
[/TR]
</tbody>[/TABLE]
This is the "102 - Soil" Sheet
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Part Description[/TD]
[/TR]
[TR]
[TD]102-PART1[/TD]
[TD="align: center"]Soil Mold[/TD]
[/TR]
[TR]
[TD]102-PART2[/TD]
[TD="align: center"]Soil Brush[/TD]
[/TR]
[TR]
[TD]102-PART3[/TD]
[TD="align: center"]Soil Tray[/TD]
[/TR]
</tbody>[/TABLE]
I need the formula to automatically look at the part number in cell A2 on "Invoice Form" and then determine the first 3 digits (Im using LEFT(A2,3). Once it recognizes the first 3 digits, I need it to look at the corresponding SHEET and then search column "A" for the matching part number and return the value in column "B" for the description.
I've tried nested if statements with vlookup and with INDEX/MATCH but I cant seem to get it to work. There are 16 different categories now, so thats a lot of nesting.
Can someone help me with this or even recommend a different approach? I'm in a bit over my skill level with this.
I have a workbook with several sheets, the first is "invoice form", this is where the formula is located.
The rest of the sheets coincide with a different categories. These sheets start with 3 numbers such as "101 - PPE", "102 - Soil", and "103 - Oven".
I have the following example:
This is the "Invoice Form" Sheet
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]Part #[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD]101-PART1[/TD]
[TD="align: center"]Formula will go here[/TD]
[/TR]
[TR]
[TD]102-PART2[/TD]
[TD="align: center"]Formula will go here[/TD]
[/TR]
[TR]
[TD]103-PART3[/TD]
[TD="align: center"]Formula will go here[/TD]
[/TR]
[TR]
[TD]104-PART4[/TD]
[TD="align: center"]Formula will go here[/TD]
[/TR]
</tbody>[/TABLE]
This is the "101 - PPE" Sheet
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Part Description[/TD]
[/TR]
[TR]
[TD="align: center"]101-PART1[/TD]
[TD="align: center"]Leather Glove[/TD]
[/TR]
[TR]
[TD="align: center"]101-PART2[/TD]
[TD="align: center"]Hard Hat[/TD]
[/TR]
[TR]
[TD="align: center"]101-PART3[/TD]
[TD="align: center"]Safety Vest[/TD]
[/TR]
</tbody>[/TABLE]
This is the "102 - Soil" Sheet
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Part Description[/TD]
[/TR]
[TR]
[TD]102-PART1[/TD]
[TD="align: center"]Soil Mold[/TD]
[/TR]
[TR]
[TD]102-PART2[/TD]
[TD="align: center"]Soil Brush[/TD]
[/TR]
[TR]
[TD]102-PART3[/TD]
[TD="align: center"]Soil Tray[/TD]
[/TR]
</tbody>[/TABLE]
I need the formula to automatically look at the part number in cell A2 on "Invoice Form" and then determine the first 3 digits (Im using LEFT(A2,3). Once it recognizes the first 3 digits, I need it to look at the corresponding SHEET and then search column "A" for the matching part number and return the value in column "B" for the description.
I've tried nested if statements with vlookup and with INDEX/MATCH but I cant seem to get it to work. There are 16 different categories now, so thats a lot of nesting.
Can someone help me with this or even recommend a different approach? I'm in a bit over my skill level with this.