I have an INDEX MATCH combination which looks up two different named ranges (direct_orders and local_suppliers), and matches the names of the publishers / suppliers with the final prices. Would it be possible to combine these two functions so it can be applied to the entire column? Below is the table.
| ORDER CALCULATIONS TEST.xlsx | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
| 1 | FOREIGN | SUPPLIERS | LOCAL | SUPPLIERS | ||||||||||||||||||||||
| 2 | DIRECT | ORDER | ||||||||||||||||||||||||
| 3 | Year | Code | Title | Minor | Lecturer | Textbook | Author | Publisher | ISBN | Quantity | Instructor | In stock | TO ORDER | CENGAGE | PEARSON | SUPPLIER1 | SUPPLIER2 | QUOTE | Supplier Final | Price Final | Availability | Tracking | COST | ARRIVED # | ||
| 4 | 1 | CS121 | Computer Organization and Architecture | Name Surname | Computer Organization and Architecture | McGraw-Hill | 5 | 1 | 0 | 6 | $ 40.00 | $ 50.00 | 1 | SUPPLIER1 | $ 40.00 | $ 240.00 | ||||||||||
| 5 | 1 | CS122 | Programming and Problem Solving II | Name Surname | Programming and Problem Solving II | Pearson | 5 | 1 | 5 | 1 | $ 50.00 | 0 | PEARSON | $ 50.00 | $ 50.00 | |||||||||||
| 6 | 1 | CS123 | Discrete Mathematics | Name Surname | Discrete Mathematics | CENGAGE | 5 | 1 | 6 | 0 | $ 60.00 | 0 | #N/A | $ - | $ - | |||||||||||
| 7 | 1 | CS124 | Discrete Mathematics | Name Surname | Discrete Mathematics | CENGAGE | 5 | 1 | 0 | 6 | $ 60.00 | 0 | CENGAGE | $ 60.00 | $ 360.00 | |||||||||||
| 8 | 1 | CS124 | Discrete Mathematics | Name Surname | Discrete Mathematics | Wiley | 5 | 1 | 0 | 6 | $ 50.00 | $ 40.00 | 1 | SUPPLIER2 | $ 40.00 | $ 240.00 | ||||||||||
Sheet2 | ||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J4:J8 | J4 | =IF(F4="No textbook",0,(COUNTIFS('ORDER CALCULATIONS MINOR COLUMNS.xlsx'!Table1[Major],"Computer Science",'ORDER CALCULATIONS MINOR COLUMNS.xlsx'!Table1[Minor],"-",'ORDER CALCULATIONS MINOR COLUMNS.xlsx'!Table1[Year],"1"))) |
| K4:K8 | K4 | =IF(F4="No textbook",0,COUNTIF(E4,"<>")) |
| R4:R8 | R4 | =IF(AND(ISBLANK(N4), ISBLANK(O4)),1, 0) |
| S4,S8 | S4 | =INDEX(local_suppliers,MATCH(T4,P4:Q4,0)) |
| T4:T8 | T4 | =IF(M4=0,0,MAX(N4:O4,MIN(P4:Q4))) |
| S5:S7 | S5 | =INDEX(direct_orders,MATCH(T5,N5:O5,0)) |
| M4:M8 | M4 | =IF(J4+K4-L4<0,0,J4+K4-L4) |
| W4:W8 | W4 | =M4*T4 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| direct_orders | =Sheet2!$N$3:$O$3 | S5:S7 |
| local_suppliers | =Sheet2!$P$3:$Q$3 | S8, S4 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| S1:S8 | Cell Value | contains "SUPPLIER2" | text | NO |
| S1:S8 | Cell Value | contains "SUPPLIER1" | text | NO |
| F1:F8 | Cell Value | contains "No textbook" | text | NO |
| P4:Q4 | Other Type | Color scale | NO | |
| J4:J8,M4:M8,W4:W8 | Cell Value | =0 | text | NO |
| S4:S8,H4:H8 | Cell Value | contains "CENGAGE" | text | NO |
| H4:H8,S4:S8 | Cell Value | contains "PEARSON" | text | NO |
| A4:O6,R4:X4,A7:S8,P5:S6,T5:X8 | Expression | =$U4="Available" | text | NO |
| A4:O4,R4:X4,A5:X8 | Expression | =$F4:$F11="" | text | NO |
| A4:O4,R4:X4,A5:X8 | Expression | =$U4="Temporarily unavailable" | text | NO |
| A4:O4,R4:X4,A5:X8 | Expression | =$U4="Print on demand" | text | NO |
| A4:O4,R4:X4,A5:X8 | Expression | =$U4="Out of print" | text | NO |
| A4:O4,R4:X4,A5:X8 | Expression | =$V4="Ordered" | text | NO |
| A4:O4,R4:X4,A5:X8 | Expression | =$V4="Arrived" | text | NO |
| A4:O4,R4:X4,A5:X8 | Expression | =$V4="Incomplete" | text | NO |
| A4:O4,R4:X4,A5:X8 | Expression | =$V4="Late" | text | NO |
