Match Item Number to Sales Data on Another Sheet?

**schweitzerc** · 06-11-2010, 01:57 PM

This is a little confusing to explain, so bear with me...

I'm creating an analysis of all the items in a catalog. To do this, I put each item number on a line in an Excel sheet along with a description, three sort down categories (example: clothing: ladies tops: tank tops), and the remaining data regarding price, margin, demand, net sales, etc.

I've had to input all of these items into our own computer system so that it can collect all of the information about the sales.

Our computer system can export the sales data into a CSV file. Is there a way I can use this to populate my fields in the Excel file? Can Excel use the item number from my table and look up the sales data from the CSV file and populate my sales fields?

I can't just copy and paste the data because I'm talking about thousands of items and chances are I missed entering one or entered it wrong and if I copy and paste I'll be off.

**JBeaucaire** · 06-11-2010, 02:07 PM

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

Here's some sample sheets showing what I think you're looking to do.

INDEX/MATCH Examples

INDEX/MATCH explanation
http://www.excelforum.com/excel-gene...ame-row.html#3

**schweitzerc** · 06-11-2010, 02:33 PM

Thanks for the quick response!

I thought I followed your example fairly well, but it didn't seem to work for me. I've attached a sample of my work. Would you be able to tell me where I'm going wrong?

There is a good chance that the first item on my page doesn't have the match to it in my sample data. So it probably won't return an accurate result... I'm just looking for the correct way to set up the formula.

I'm pretty new at this advanced Excel stuff, but I'd like to think I learn quickly!

**schweitzerc** · 06-11-2010, 02:36 PM

Any time I edit this formula it also seems to open a dialog box asking me to "Update Value in: DATA"

I'm not sure what thats about

EDIT: Looks like it doesn't like my Worksheet Name. Changing it to just "DATA" corrected this problem, but I still have an issue in the formula

**JBeaucaire** · 06-12-2010, 12:52 AM

1) To put a multi-word reference in a formula, you have to put single quotes around the words:

INDEX('RAW QOP DATA'!H:H...

2) What are you trying to match B10 to on the data sheet? Which column? I don't see the B10 value anywhere on that sheet.

=MATCH(Value, RangeOfValues, 0)

Value = B10 (the value to match)
RangeOfValues = the column of values the value to match must be found in)
0 = False, means the match has to be exact

**schweitzerc** · 06-14-2010, 10:42 AM

What I'm attempting to do is Match the SKU/Item Number on Sheet 2 (Column B) to the SKU/Item Number on the raw data page (Column A) and then lift the Demand Units, Ship Units, Return Units, and Demand Gross Profit out of the raw data and report to Sheet 2.

But keep in mind, in my sample there may not be a match to the data since the sample size is so small. I'm just looking for formula integrity.

Perhaps I didn't understand the formula as much as I thought and totally jacked it up beyond recognition!

Any help would be appreciated.

Thanks!

**JBeaucaire** · 06-14-2010, 12:33 PM

Here's an example of the corrected formula for J10:

=IF($B10="", "", INDEX('RAW QOP DATA'!H:H, MATCH($B10, 'RAW QOP DATA'!$A:$A, 0)))

Notice I had to add all the single quotes around the sheet names? Copy this formula down and across the 4 columns and you will get:

1) #N/A - the match is not available
2) valid response...none of your sample data matched so far