Index/Match Excel 2010 formula. Some numbers have "###", others not, ###

[thecdnmole]

I am using Index/Match to lookup values in a spreadsheet. There are duplicates that it needs to lookup and decipher which is first and second, so I used a formula to add a 1 or 2 to the original number. I then copy and paste value to remove the formula. Everything works good until I manually change the numbers to reverse them, then Index/Match does not find it. I noticed the number I copy/paste value looks like this for example, "1234", whereas the number I manually enter looks like this, 1234. Of course Excel thinks these are two different numbers, but why the difference when using copy/paste value compared to manually entering the number and how do I make this work if something has to be manually changed for the Index/Match to work?

Here is a screenshot of what I mean.
Attachment 403745

[FDibbins]

cant see your pic, but have you tried to make the columns wider?

edit: OK, I guess it helps to read the whole post, not just teh title, sorry

As you found, INDEX/MATCH and VLOOKUP only find teh 1st instance of what you are looking for, and then stops looking. By adding a "counter" to the end of the numbers is 1 way of pulling out all duplicates, but by removing the counter, you - removed the counter - which means all duplicates are identical again.

There are other ways of doing this though, so can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[thecdnmole]

No, but I don't think that would help. I have attached a sample. Index/Match formula is in A1 on sheet1, chart on sheet2 and lookup value in A1 sheet3.

[Tony Valko]

The problem is that you have 2 different data types.

The lookup value on Sheet3 is a numeric data type (a number) but the data in the table array is mixed. C1 is a numeric number but C2 is a TEXT STRING even though it LOOKS like a number.

Notice the cell alignment. C1 (the number) is aligned right but C2 (the text string) is aligned left.

Also, the formulas in D1:D2 return TEXT stings, not numbers.

So, how is the table array created? By formulas? Copy/pasted? Imported from another application? Manually entered?

[thecdnmole]

Thanks Tony for the reply. If I change the format in C1:C2 to number or text, the formula still does not work. I copy the formula in column D on the actual spreadsheet and then copy/paste value back onto itself. The value in column B is manually entered. How do I make the formula in column D return a number? Or, how do I get it all in text or in number? Thanks for the help.

[Tony Valko]

Simply changing the cell format has no effect unless/until you edit the cell(s) (re-enter the data).

You can coerce the formulas in D1:D2 to return numbers like this:

=--(B1&COUNTIF($B$1:$B1,B1))

However, if any of the numbers have leading zeros then they will be stripped off. If you need leading zeros then you'll have to make the data TEXT. You would also want to make the lookup value text.

[thecdnmole]

Thanks again, Tony! I got it to work on my sample, but not on the actual. I then remembered I have yet another formula, which take the original value, such as the number enter on sheet3 and adds a 1 or two to match what's on sheet2. This is the formula I should have put in A1 of sheet3, which then could reference B2 of sheet 3 for the source. "='Facilities(TC331)'!H7&2"

[JeteMc]

If I am understanding the basic problem, you need to search for a particular instance of a value. I think that can be accomplished by making a change in the way that you look up the values. Notice that I have changed sheet3 from one input cell to two, the first looks up value, the second indicates which instance of that value you want to find. Since it would be hard to know which instance is found I also added a second cell in sheet1 that lets you know whether you are looking at the first (a) or second (b) instance.

update of Index_Match.xlsx

Hope this helps.

[thecdnmole]

I figured it out! I changed "='Facilities(TC331)'!H7&2" to "=--('Facilities(TC331)'!H7&2)" and now it works 100%! Awesome, thanks again Tony!

[thecdnmole]

Thanks JeteMc, looks like your method works as well, but does contain array formula which I would prefer to stay away from or not use if possible. Tony had a fairly simple fix which works, so I am going to use his method. Appreciate the response and suggestion as it might work for someone else's case.

[Tony Valko]

You're welcome. Thanks for the feedback!

[JeteMc]

That's why Tony is a Guru. Main thing is that you get a solution that works for you. If your original question has been answered please mark the thread "Solved". Also please add to Tony's and FDibbins' reputations as they made important contributions to the solution.

[thecdnmole]

Sorry FDibbins, I did not see that you edited your reply! Thanks for responding so quick and I am curious what other way, maybe easier or better way is there to count duplicates? Thanks to all, have a wonderful weekend!!