ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for others

I have been putting together an Excel file to help our customers better understand our contract pricing (yes, I know this is a problem in and of itself...) with them. To do this, I decided that I was going to use a lot of Index+Match+Match type formulas to basically return a lot of data based on only entered 2 cells of information.

For the purpose of my problem I will only show a couple.

The 2 columns Collection and SKUs are returning a match for the wrong value, but only on a couple of different entries which is really confusing me. There are no duplicate entries in my worksheet to cause Index to be confused on what to return.

They are both using Index+Match equations.

Here are the 2 columns where the formulas are placed:

Where the formulas are entered, columns D and E - http://i.imgur.com/CduHty3.png

The data that the Index is for the match to look in - http://i.imgur.com/Ap7t8OB.png


Here are the equations:

SKU Formula - http://i.imgur.com/F8YbsZe.png

Collection Formula - http://i.imgur.com/rxw8GHL.png


As you can see - when I type in Modern Blaze it will return the collection name and SKU value for Milano...yet when I type in Milano it comes back with the correct collection name and SKU value for Milano like it is supposed to.

A couple other examples are also on the pictures.

Any thoughts...?

Here are the formulas typed out in case any one wants to mess with them to assist.

SKU FORMULA

=IF(ISERROR(INDEX('Unit Costs'!$Q$1:$Q$98,MATCH(B2,'Unit Costs'!$O$1:$O$98,0))),"",INDEX('Unit Costs'!$Q$1:$Q$98,MATCH(B2,'Unit Costs'!$O$1:$O$98,0)))


COLLECTION FORMULA

=IF(ISERROR(INDEX('Unit Costs'!$P$1:$P$98,MATCH(B2,'Unit Costs'!$O$1:$O$98,0))),"",INDEX('Unit Costs'!$P$1:$P$98,MATCH(B2,'Unit Costs'!$O$1:$O$98,0)))

Pictures aren't very helpful here, because we can't copy/paste values from the picture into our own workbooks for troubleshooting.

Can you attach an actual sample workbook?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I would but it has a lot of pricing information thrown throughout -- but you are telling me there is no way to look at the equation and the pictures to see any issue?

Nope, I can't see anything wrong with the formulas (or the data) shown in the pictures you posted.
In fact, I don't even see a formula that is returning an incorrect result...

If you can work out a trimmed version of the file (still displaying the problem).
We only need to see a few rows of data.
Replace real names/numbers with bogus made up information.

Originally Posted by Jonmo1

Pictures aren't very helpful here, because we can't copy/paste values from the picture into our own workbooks for troubleshooting.

Also, many people here are unwilling/unable to view documents stored on a 3rd party file sharing site.
But we tend to trust documents more if they are actually stored on excelforum.com's own server.

For Help.xls

Here! Hopefully this should help.

You have extra spaces in alot of the cells in the Units Costs tab, column A.
Example, A56 is actually "Modern Blaze " instead of "Modern Blaze"
But the value in B2 on Sheet2 is "Modern Blaze"

"Modern Blaze" does NOT match "Modern Blaze "

Is there an easy way for me to fix this? Is this mostly what the =TRIM formula is for?

Originally Posted by kuunamatata

Is this mostly what the =TRIM formula is for?

Yes, TRIM would be ideal for that.

On the Units Cost tab, in any available column (say G)
In G2 and filled down to the end of your data put
=TRIM(A2)

The copy column G, and Paste Special - Values on top of column A.

Geez. I feel so dumb now. I knew it had to be something as simple as this.

Thanks for your help, Jonmo1.

You're welcome.