Please assist with vlookup, im not sure why I am getting n/a. list formulas!

my spreadsheet:

Johnson Equipment, the medium-sized laboratory equipment manufacturing company at which you work, is in the process of acquiring Sloan Manufacturing, a smaller equipment manufacturer in the same industry. Because operations between your company and Sloan overlap, you need to merge the data from the new company with similar data for your company. The text file you received from Sloan contains categories, product numbers, and product descriptions, in addition to the on-hand quantity for each product and the number of products produced during each month of the past year. You need to add prefixes to the category names so the data will match the existing data that your company uses. Then, you will use Excel to organize and summarize the data. Complete the following: Use functions & features from Chapter 7 (and previous chapters) to complete the tasks below.

1. Include the data from the Sloan.txt data file on a worksheet named Data. Tip: You can use the Text to Columns (Alt, a, e) feature if it is necessary to parse the data in separate columns.

2. Sort the data by product number in ascending order (smallest to largest) (Alt, a, s, a). Also make it so that when you use the worksheet scroll buttons, that the column headings, Category names and Product Number are always visible. Make it so that you can see the full Description for each Category/Product. However, do NOT make the Description column really, really wide. (Make really long text wrap to the next line.) Top Align all text and data so that the contents of each cell is aligned at the top of each cell.

3. Create "New Category" names in the "Data" sheet by adding the appropriate prefix and a dash to each category using the following list. For example, the new Analyzer category would begin with 500, followed by a dash and the category name: 500-Analyzer. If the Category listed in the data file does NOT have an associated Prefix, the "New Category" should display #N/A, indicating that a Prefix has not yet been assigned. Note: You should first copy the data from the list below and paste it into a new worksheet named Prefixes, then parse the data into separate columns. Hint 1: Data you need to get into Excel is not always in a Delimited file. But you still should be able to use the Text to Columns tool (Alt, a, e) to parse the data into separate columns. Prefix Category 500 Analyzer 501 Autoclave 503 Balances 505 Bath 507 Biohood 509 Cell Disrupters 511 Cell Harvesters 513 Centrifuges 515 Chromatography 517 Desiccators 519 Evaporators 521 Fermentors 523 Furnace 525 Gas Chromatographs 527 Glove Boxes 529 Microscopes 531 Reactors 533 Spectrophotometers 535 Ultrasonic Cleaners Do NOT swap the order of the columnsleave them in your spreadsheet in the columns as shown above. Hint 2: To assign the new Prefixes, first insert a new column in the "data" sheet to the left of the data you imported and label it with a column heading of "Prefix". Figure out which function or functions (from a previous chapter) to use in order to match up the Category (in column B) with the appropriate Prefix. You will need to refer to the "Category/Prefixes" table (on the Prefixes sheet) to properly match up the Category name with the correct Prefix. (If the Category listed in column B does not exist, be careful that the function you choose does not assign an incorrect Prefix.) Hint 3: To create the new category names, first insert another new column in the "data" sheet to the left of the Prefixes column and include a column heading: "New Category". Within this column (now column A), use the CONCATENATE function or the CONCAT function or the & symbol to create the new category name based on the Prefix in column B, and the old Category name in column C. Below are screenshots:

4. Change the entire range on the sheet named Data into an Excel Table (Ctrl+t OR Alt, h, t ...) picking whatever Table style you like. (An Excel "Table" was known as a "List" in previous versions of Excel & MAC Excel). WARNING: Do NOT use the Select All button (upper left corner) to select the entire sheet (all rows and/or columns) before Formatting as a Table! Only select the cells that contain actual data or formulas. Selecting all 1,048,576 rows and/or 16,384 columns before creating a Table can overload Excel.

5. Use Excel Table's Total Row feature to calculate the number of units on hand and the number of units produced in each month. (If you have inserted a title & a blank row below the title, your column headings should start in row 3. The "Total row" will be created at the bottom of the table in a row below your data.) Then also add a new column named Total to the right of the December column that calculates the number of units produced for the year for each part. (Use a SUM function to calculate Total Production for all months and for the year...but when summing, do NOT sum the Quantity on Hand.)

6. Display the top 20 items based on the values in the Total column to show the parts with the highest productions. Hint: Use Autofilter's "pull-down arrows" (Alt+down_arrow, NumberFilter, Top10) to show the Top 20 items...all the rest of the items will be hidden from view. Leave your spreadsheet showing only the Top 20 items when you submit your HW, to prove that you were correctly able to filter the list.

7. Since you have a New Category name, you no longer need to see the Prefix and "old" Category columns. Make it so that those two columns are not showing, but anyone should be able to quickly show (& un-show) those columns (using +/- buttonsnot by manually hiding/-unhiding).

8. Include both the Data and Prefixes worksheets in the assignment file you will submit. But hide the Prefixes sheet. (In the Documentation sheet, link to this sheet before hiding it. Then hide the row in the Documentation sheet containing the link to Prefixes sheet.)

board Font Alignment V A Prefix #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Data Analysis Sensi E Quantity on-hand 31 68 90 57 3 35 90 82