Hello!
I am trying to create a summary tab in excel that will look up data in other tabs based on specified criteria, but the structure of those other tabs and location of data is not always consistent. I’ve used a combination of Vlookup, Index, and Match to achieve the desired result, but I was wondering if there was a more streamlined way to do it, or if anyone else had any other ideas. I know it would make the most sense to structure each tab in the same way, but that is not an option in this circumstance.
My current solution finds the location of the Manufacturer name row, matches the brand, and then pulls the price on to the summary tab. I feel like there is a simpler way to identify the row with Manufacturer name. Its currently “MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)&":"&MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)” Its basically the same formula twice to return a row reference of #:#.
I have attached a sample file for your reference. The formula in question is in yellow. Thank you in advance for any ideas or suggestions.
I am trying to create a summary tab in excel that will look up data in other tabs based on specified criteria, but the structure of those other tabs and location of data is not always consistent. I’ve used a combination of Vlookup, Index, and Match to achieve the desired result, but I was wondering if there was a more streamlined way to do it, or if anyone else had any other ideas. I know it would make the most sense to structure each tab in the same way, but that is not an option in this circumstance.
My current solution finds the location of the Manufacturer name row, matches the brand, and then pulls the price on to the summary tab. I feel like there is a simpler way to identify the row with Manufacturer name. Its currently “MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)&":"&MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)” Its basically the same formula twice to return a row reference of #:#.
I have attached a sample file for your reference. The formula in question is in yellow. Thank you in advance for any ideas or suggestions.
Sample.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
4 | Financial Summary | |||||||||
5 | ||||||||||
6 | Tab Name | |||||||||
7 | Compact Cars | Compact Car Brands | Toyota | Honda | Lexus | |||||
8 | Annual Premium | $31,800 | $34,450 | $34,980 | ||||||
9 | ||||||||||
10 | SUV | SUV Brands | Toyota | Honda | Lexus | |||||
11 | Annual Premium | $47,700 | $50,880 | $52,470 | ||||||
12 | ||||||||||
13 | Total | Toyota | Honda | Lexus | ||||||
14 | Total Annual Premium | $79,500 | $85,330 | $87,450 | ||||||
Financial Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C8,E8,G8,C11,E11,G11 | C8 | =VLOOKUP("Price with Tax",INDIRECT("'"&$A7&"'!A6:ZZ100"),MATCH(C7,INDIRECT("'"&$A7&"'!"&MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)&":"&MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)),0),FALSE) |
D8,F8,H8,D11,F11,H11 | D8 | =VLOOKUP("Annual Premium ",INDIRECT("'"&$A7&"'!A6:N70"),MATCH(D7,INDIRECT("'"&$A7&"'!"&MATCH("Carrier Name ",INDIRECT("'"&$A7&"'!A:A"),0)&":"&MATCH("Carrier Name ",INDIRECT("'"&$A7&"'!A:A"),0)),0),FALSE) |
C10,C13 | C10 | =$C$7 |
E10,E13 | E10 | =$E$7 |
G10,G13 | G10 | =$G$7 |
C14,E14,G14 | C14 | =SUM(C8,C11) |
D14,F14,H14 | D14 | =D8+D11+#REF!+#REF!+#REF!+#REF! |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B1:DW40 | Cell Value | contains "☻" | text | NO |
Sample.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | ||||||||||||||
2 | ||||||||||||||
3 | Compact Cars | |||||||||||||
4 | ||||||||||||||
5 | ||||||||||||||
6 | Manufacturer Name | Toyota | Honda | Lexus | ||||||||||
7 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
8 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
9 | ||||||||||||||
10 | Car Price | $30,000 | $32,500 | $33,000 | ||||||||||
11 | Price with Tax | $31,800 | $34,450 | $34,980 | ||||||||||
Compact Cars |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E11,K11,H11 | E11 | =E10*1.06 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
P9:DS9,A9:N9,A10:DS38,A1:DS8 | Cell Value | contains "☻" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
K7:L8 | List | $0,$25,$50,$75,$100 |
E7:F8 | List | $0,$25,$50,$75,$100 |
H7:I8 | List | $0,$25,$50,$75,$100 |
Sample.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | SUV | |||||||||||||
2 | ||||||||||||||
3 | ||||||||||||||
4 | ||||||||||||||
5 | ||||||||||||||
6 | ||||||||||||||
7 | ||||||||||||||
8 | Manufacturer Name | Toyota | Honda | Lexus | ||||||||||
9 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
10 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
11 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
12 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
13 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
14 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
15 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
16 | Information Section | "data" | "data" | "data" | "data" | "data" | "data" | |||||||
17 | ||||||||||||||
18 | Car Price | $45,000 | $48,000 | $49,500 | ||||||||||
19 | Price with Tax | $47,700 | $50,880 | $52,470 | ||||||||||
20 | ||||||||||||||
SUV |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E19,K19,H19 | E19 | =E18*1.06 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A11:DS11 | Cell Value | contains "☻" | text | NO |
A12:DS13 | Cell Value | contains "☻" | text | NO |
A14:DS14 | Cell Value | contains "☻" | text | NO |
P17:DS17,A17:N17,A18:DS46,A1:DS10,A15:DS16 | Cell Value | contains "☻" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
H9:I16 | List | $0,$25,$50,$75,$100 |
E9:F16 | List | $0,$25,$50,$75,$100 |
K9:L16 | List | $0,$25,$50,$75,$100 |