Multiple nested IF STATEMENTS with NESTED VLOOKUP or INDEX?MATCH with LEFT in each nest

[rspradley]

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

Part #	Description
101-PART1	Formula will go here
102-PART2	Formula will go here
103-PART3	Formula will go here
104-PART4	Formula will go here

<tbody>
</tbody>

This is the "101 - PPE" Sheet

Part Number	Part Description
101-PART1	Leather Glove
101-PART2	Hard Hat
101-PART3	Safety Vest

<tbody>
</tbody>

This is the "102 - Soil" Sheet

Part Number	Part Description
102-PART1	Soil Mold
102-PART2	Soil Brush
102-PART3	Soil Tray

<tbody>
</tbody>

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.

 

[mikerickson]

It would help if you renamed the category sheets to something consistant, like "Category 101" "Category 102" etc.

Then

=VLOOKUP(A2, INDIRECT("'Category "&LEFT(A2,3)&"'!$A:$B"), 2, FALSE)

could be used.

 

[rspradley]

It would help if you renamed the category sheets to something consistant, like "Category 101" "Category 102" etc.

Then

=VLOOKUP(A2, INDIRECT("'Category "&LEFT(A2,3)&"'!$A:$B"), 2, FALSE)

could be used.

Mike, thank you. I'll be ok with the category names as they are, and the issue is the nesting to recognize the difference between the 16 different categories. It needs to look up on a different page depending on those first 3 numbers.

How can this be done with multiple if statements/lookups nested in 1 formula? Or any other way that would work?

 

[mikerickson]

The problem is that each part number references only the three digit start, e.g. "101".

To search the sheets to find out which sheet name starts with "101" takes VBA if there are different endings to the sheet names, e.g. "101 PPE" "102 Soil"
If each sheet has the same ending, like "101 category" "102 category", then the built in INDIRECT can handle it.

With varying sheet name suffixes, you will need VBA. There is no native Excel solution.

 

[rspradley]

Thank you Mike,

So I can change the sheet names to make it work. I have a question about indirect, is it limited? I ask between the 16 different sheets, there are thousands of products. Will INDIRECT still work?

Could you help with the VBA option?

 

[mikerickson]

The UDF solution might involve something like

Function IndirectSheetPrefix(byVal SheetPrefix as String, byVal RangeAddress as String, Optional WorkbookName as String) As Range
    Dim wb as Workbook, ws as WorkSheet
    Application.Volatile

    SheetPrefix = LCase(SheetPrefix)
    If WorkbookName = vbNullString Then
        set wb = ThisWorkbook
    Else
        Set wb = Workbooks(WorkBookName)
    End If

    For Each ws in wb.Worksheets
        If LCase(ws.Name) Like SheetPrefix & "*" Then
            Set IndirectSheetPrefix = ws.Range(RangeAddress)
            Exit For
        End If
    Next ws
End Function

And a formula like
=VLOOKUP(A2, IndirectSheetPrefix(LEFT(A2,3), "$A:$B"), 2, FALSE)