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

#### gmrush

##### New Member
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.

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!

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):

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

<tbody>
</tbody>

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:

 A B Item Cost 00023 50.00 009450 2,792.51 009452 1,882.50 009460 3,377.50 009462 3,227.50 01038 1.48 05196 47.50 056008 24.75 075034 0.14 075036 0.14

<tbody>
</tbody>

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.

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

Replies
4
Views
308
Replies
10
Views
742
Replies
3
Views
452
Replies
9
Views
319
Replies
3
Views
378

1,207,255
Messages
6,077,307
Members
446,278
Latest member
hoangquan2310

### 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.

### Which adblocker are you using?

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

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