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

rspradley

New Member
Joined
Feb 3, 2019
Messages
3
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
The UDF solution might involve something like

Code:
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)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top