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

gmrush

New Member
Joined
May 27, 2015
Messages
13
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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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!
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top