HELP! Search for String from Col A, Tab 2 in Col F, Tab 1 and Return Value in Col B, Tab 2

[gmrush]

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

TAB 1, COMMISSIONS

Commissions1.PNG

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:

TAB 2, COGS LIST

Commissions2.PNG

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.

NOTE: The COGS list tab contains approximately 4,460 entries/items.

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.

[daffodil11]

=SUMPRODUCT((ISNUMBER(SEARCH(Sheet2!F2,Sheet2!$A$2:$A$4460)))*(Sheet2!$B$2:$B$4460))

and copy down

[gmrush]

Thank you for your response. My goal is not to perform a calculation, but to retrieve the cost of an item (from Col B of the COGS List tab) and copy it into Col J of the Commissions tab based upon a "match" of the item number (those listed in Col A of the COGS List Tab) as compared with text within each of the cells in Col F of the Commissions Tab.

[daffodil11]

Have you tried the formula?

Just because something calculates a SUM or a PRODUCT doesn't mean it's every adding up more than a single quantity. Assuming that part number only appears in the COGS list once, it will calculate a single quantity everytime. The criteria and arrays remove all non-conforming values.

The exact same logic which only works for words instead of numbers:

=LOOKUP(2,1/(ISNUMBER(SEARCH(Sheet2!F2,Sheet2!$A$2:$A$4460))),Sheet2!$B$2:$B$4460)

[gmrush]

Ah! Ok! I was a bit confused when I read about SUMPRODUCT and tried to envision and step through the formula to see what it was doing. Will give it a try and post back shortly! Thank you for the clarification!

[gmrush]

Ok...the SUMPRODUCT formula returns a value of 0 for every cell. The LOOKUP formula keeps giving me a #N/A.

[daffodil11]

Perhaps you can upload a densensitized version of your data? Just give me enough of your real world scenario to build it.

I've attached my example. If it doesn't help, I'd suggest you add one of your own.

[gmrush]

I have provided a very small sample of the data contained within the "actual" Excel worksheet so that you can see how it is set up. Again, the value we are trying to look up is the cost (COGS) in col B of the COGS List tab, and then move that value to the respective cell in Col J based on a "match" of some information contained in Col F of the Commissions Tab with the information contained in a respective cell in Col A of the COGS List.

Sample Commission Tracking Worksheet 052815.xlsx

[daffodil11]

Appears to work as intended. Maybe your ranges were different.

I've included some tutorials on SUMPRODUCT and LOOKUP, which will help take you to the next level with formulas. =)

[gmrush]

The spreadsheet I attached has all of the values/ranges so that you have a full understanding of the layout.

[daffodil11]

I included your example in my last post, modified to work as you need.

I name all downloads Unicorn something so I know what to go back and delete each week. I pull a few hundred examples off the boards in short order and go back through to clean up.

[gmrush]

I put your formula into the spreadsheet, and for example, the first item in Col F of Commissions (i.e., part number 51800-55) should have had a cost of $182.48, and yet the formula is returning a value of $202.40? I'm not sure why that is or where it is getting that value from.

The formula, as I have it set up in my worksheet, is:

=SUMPRODUCT((ISNUMBER(SEARCH('COGS List'!$A$2:$A$3972,F2)))*('COGS List'!$B$2:$B$3972))

[daffodil11]

That would indicate that more than one of the COGS id's is an exact match.

[gmrush]

I used your error checking formula and now it seems to be working. I have no idea why it was hanging up on the other formula, but now things are falling into place! THANK YOU! You're amazing!

[daffodil11]

Awesomesauce!

[gmrush]

I've run into another snag, and am hoping you can help me out. Now that the COGS amount is being correctly looked up, I am running a calculation to determine commissions. The commission, however, is dependent upon whether or not the sale was commissionable, there is a Y or N value in one column, and whether or not there is a blank cell. For example, given this data:

SAMPLE IMAGE

SpreadsheetSample1.PNG

The formula calculating the commissions in N2 (i.e., $99.11) is as follows:

=IF(G2="N",0,IF(G2="Y",M2*L2,IF(L2<0,L2,M2*L2)))

That works well as long as you don't have a negative amount in cell L2, as in row 2. If you apply the above formula to row 2, the result is ($3.43), when it should return ($8.57). The formula that works correctly for row 2 (i.e., cell N2) is:

=IF(H2="",0,IF(L2<0,L2,IF(G2="N",0,M2*L2)))

But then we run into the problem that we have another condition, as in rows 3 and 4, where cell H3 and H4 have no value, and therefore must use this formula:

=IF(G3="N",0,IF(H3="",0,IF(L3<0,L3,M3*L3)))

I've tried to combine the three formulas to do multiple tests, but just cannot seem to get the syntax correct. Essentially:
1) If a value in Col G has an "N", the result in Col N must be $0.00.

2) If a value in Col G has a "Y", the result in Col N needs to multiply Col M * Col L AS LONG AS Col L is not a negative number. If the value in Column L is a negative number, the result in Col N must be exactly equal to the value in Col L.

I can't seem to wrap my head around the syntax for the formula. ANY help you can provide would be VERY MUCH appreciated.

SAMPLE SPREADSHEET

Sample Commission Tracking Worksheet 052915.xlsx