Clean Product Search
Part of the Formulas and Data Analysis section of Coddy's Excel journey. Lesson 27 of 28.
Challenge
HardThe unique product table is in A2:C4. The requested code in E2 may contain surrounding ordinary spaces; quantity is in G2. In F2, return the product name for the trimmed code. In H2, return price times quantity rounded to two decimals. For an absent code, both outputs must say Unknown code.
Enter formulas in the highlighted output cells: F2, H2. Keep the supplied data and headings. Tests change input values, so use cell references instead of typing the sample answers. Use English function names and commas between arguments.
Try it yourself
Sheet1
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Search | Result | Quantity | Total | |
| 2 | P3 | Pad | 8 | P1 | 3 | |||
| 3 | P1 | Pen | 3 | |||||
| 4 | P2 | Clip | 5 | |||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 | ||||||||
| 11 | ||||||||
| 12 | ||||||||
| 13 | ||||||||
| 14 |
All lessons in Formulas and Data Analysis
Practice on your own: Excel playground