Multiple If statements with multiple then statements pulling from Index/Match commands

Something like this:

=IF(left(model number,1)="V",INDEX('M:\Echart\[EChart 1053 Trim Code Sheet Rev 19.xls]Trim Codes with MCS'!B$3:B$1053, MATCH(I$11, 'M:\Echart\[EChart 1053 Trim Code Sheet Rev 19.xls]Trim Codes with MCS'!$A$3:$A$1053,0)),INDEX('[EChart 1049 Oracle Trim Code Sheet Nextech.xlsx]Trim code with temps'!B$2:B$215,MATCH($B$23, '[EChart 1049 Oracle Trim Code Sheet Nextech.xlsx]Trim code with temps'!$A$2:$A$215,0)))

Post a workbook. How many types do you have?

---

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

In the IF() formula, it tests for "V", if "V" is found, the test returns TRUE, and the first Index function is evaluated/executed, if not, the second is. However, I suspect there are many more of these codes, so the IF statement may not work; thus the request for a workbook.

Testing for three criteria requires a 2-level nested if formula:

=IF(LEFT(E15,1)="V",INDEX('M:\Echart\[EChart 1053 Trim Code Sheet Rev 19.xls]Trim Codes with MCS'!B$3:B$1053,MATCH(I$11,'M:\Echart\[EChart 1053 Trim Code Sheet Rev 19.xls]Trim Codes with MCS'!$A$3:$A$1053,0)),IF(LEFT(E15,1)="N",INDEX('M:\Echart\[EChart 1049 Oracle Trim Code Sheet Nextech.xlsx]Trim code with temps'!B$2:B$215,MATCH(I$11,'M:\Echart\[EChart 1049 Oracle Trim Code Sheet Nextech.xlsx]Trim code with temps'!$A$2:$A$215,0)),INDEX('M:\Echart\[Echart 1048 Trim Code Sheet PSG Rev1.xls]Trim Codes'!B$7:B$16,MATCH(I$11,'M:\Echart\[Echart 1048 Trim Code Sheet PSG Rev1.xls]Trim Codes'!$A$7:$A$16,0))))

The first IF tests for V, and, if found, the first Index is evaluated. If V is not found, the inner IF tests for N, if N is found, the first Index of the inner IF function is evaluated, if not, then P is assumed and the last Index is evaluated.

An alternative is the Choose function will branch to a specific Index function based on the position the test code (Left(E15,1)) is found in the lookup array:

=CHOOSE(MATCH(LEFT(E15,1),{"V","N","P"},0),INDEX('M:\Echart\[EChart 1053 Trim Code Sheet Rev 19.xls]Trim Codes with MCS'!B$3:B$1053,MATCH(I$11,'M:\Echart\[EChart 1053 Trim Code Sheet Rev 19.xls]Trim Codes with MCS'!$A$3:$A$1053,0)),INDEX('M:\Echart\[EChart 1049 Oracle Trim Code Sheet Nextech.xlsx]Trim code with temps'!B$2:B$215,MATCH(I$11,'M:\Echart\[EChart 1049 Oracle Trim Code Sheet Nextech.xlsx]Trim code with temps'!$A$2:$A$215,0)),value3)

For instance if E15 is N... then Match(E15... returns 2 and Choose evaluates the second Index in the list.