Stumped - Search for String from One Column in Another Column and Return a Value From Another Column When Found

[gmrush]

I've got a rather sticky situation with a spreadsheet that is calculating sales commissions.

1) Within my spreadsheet, I have a tab (COGS List) that contains all of our inventory items (by item number), their cost, and their price (Columns A, B, and C respectively).

2) I have another tab that has other relevant commissions information, including the Item Description (Column F) as it comes out of Quickbooks, and Column J (cost of the item as referenced from COGS List tab).

I am trying to figure out what formula I would use to take the Item Number from Column A of the COGS List tab and search for it in the Item Description column (Column F) of the Commission Tab and then based on a successful match, copy the associated cost from the COGS List cell into Column J of the Commissions Tab.

Unfortunately, Quickbooks has horrible custom reporting abilities and the ODBC interface continuously hangs and shuts down. Therefore, we are attempting to devise an Excel workaround for the interim.

ANY assistance on this would be greatly appreciated!

 

[bob33]

I think one solution would be an Index Match formula. A bit tricky to use but google "index match excel" and you'll see several how to's.
If still stumped, best to post an abbreviated sample workbook with representative data.

 

[gmrush]

I think one solution would be an Index Match formula. A bit tricky to use but google "index match excel" and you'll see several how to's.
If still stumped, best to post an abbreviated sample workbook with representative data.

Thank you, Bob! Will do!

 

[gmrush]

Bob:

I have looked at the Index Match examples, and cannot wrap my head around how to make it work with my current situation. Here is the detailed information you requested.

In an Excel spreadsheet, I have two separate tabs that are needed to help calculate commissions. This is a partial of the information on the first tab (Commission):

[TABLE="width: 576"]
<tbody>[TR]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]Item
[/TD]
[TD]Comm
[/TD]
[TD]Qty
[/TD]
[TD]Sale Price
[/TD]
[TD]Cost
[/TD]
[/TR]
[TR]
[TD]Soap (Assemblies):51800-55 (POWER SHINE 55 GALLON)
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[TD]$430.25
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]51450-55 (POWER SHINE PLUS, 55 GAL)
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[TD]$315.50
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]920795 (PUMP SIPHON)
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[TD]$0.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pressure Washers:555SS (Portable Hotsy Hot Water Electric Pressure Washer - 2.2 GPM @ 1300 PSI, ...
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[TD]$2,995.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]374232 (TURBO NOZZLE 4300/4.0)
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[TD]$0.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Soap (Assemblies):54350-5 (FLEETWASH, 5 GALLONS)
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[TD]$0.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]368880 (LANCE, ST-85 PUSHPULL LANCE)
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[TD]$200.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spec Part II (Special part purchase)
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[TD]$0.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]87390180 (HOSE 3/8x100FT,3/8MSWBE GR)
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[TD]$75.00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Notice that the first column, Item, contains the information exported from Quickbooks with includes the part number, usually the first number before the descriptive text (i.e., 51450-55 for the Power Shine in Row F3, but sometimes follows part of the description (i.e., :51800-55 in Row F2). At the moment I have the sale price of the item (as per the sales invoice in Quickbooks); however, do not have the associated COGS (cost) amount, which is contained on the second tab (COGS List) as shown below:

[TABLE="width: 267"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]Item
[/TD]
[TD]Cost
[/TD]
[/TR]
[TR]
[TD]00023
[/TD]
[TD]50.00
[/TD]
[/TR]
[TR]
[TD]009450
[/TD]
[TD]2,792.51
[/TD]
[/TR]
[TR]
[TD]009452
[/TD]
[TD]1,882.50
[/TD]
[/TR]
[TR]
[TD]009460
[/TD]
[TD]3,377.50
[/TD]
[/TR]
[TR]
[TD]009462
[/TD]
[TD]3,227.50
[/TD]
[/TR]
[TR]
[TD]01038
[/TD]
[TD]1.48
[/TD]
[/TR]
[TR]
[TD]05196
[/TD]
[TD]47.50
[/TD]
[/TR]
[TR]
[TD]056008
[/TD]
[TD]24.75
[/TD]
[/TR]
[TR]
[TD]075034
[/TD]
[TD]0.14
[/TD]
[/TR]
[TR]
[TD]075036
[/TD]
[TD]0.14
[/TD]
[/TR]
</tbody>[/TABLE]

NOTE: There is NOT a one to one correlation of the item listed in column A of the COGS List tab with the item listed in Column F of the Commissions tab (in other words, yes...one item can only have one cost, but the way the items appear in the COGS list are numerically ordered and their occurrence in the Commission List is random with regard to the date on which the sale was made).

I need to be able to:

1. Take the item number in Col A of the COGS List;
2. Search for that item number within the content of the cells in Column F of Commissions;
3. When a match is found, copy the corresponding cost from Col B of COGS List to Column J of Commissions.

Any assistance would be available. I absolutely could not figure out how to use INDEX MATCH to accomplish this. There might be an easier method, but do not have access to VBA or Access. My software "tools" are limited.

 

[gmrush]

I needed to add that the COGS list tab contains approximately 4,460 entries.