Function MATCH issues due to number type.

[namy77]

Hi, this is a screenshot of my 2 dimensional Match and Lookup - using 2 variables to derive at a cell. Everything works except for cases where the second variable is a 1.0, 2.0 or 3.0 - because I can't format these as numbers (it will truncate to 2 rather than keep 2.0) I format these fields as text, but MATCH seems to have a hard time matching 2.0 with 2.0 when they are both text.

00000034.png
Any suggestions? Thanks!

Screenshot: http://screencast.com/t/WtVfW6CY6

[MrShorty]

It can be difficult to debug from a picture. I can see that 2.0 in the lookup table is probably "number stored as text". It is not as clear to me if the 2.0 value in C3 -- the lookup value -- is also a number stored as text. If I had a spreadsheet to test on, that would be the first test I would make. Make absolutely sure that both the lookup value and the values in the lookup table are text.

[namy77]

Alright - here - I formatted them as text and all the N.0 ones work - but the others dont, and when i format it as number - its the other way around.

Thanks!

[FDibbins]

Why not just format as General, and set to 1 decimal place? The Match will then find whatever you enter.

MATCH seems to have a hard time matching 2.0 with 2.0 when they are both text.

The only numbers ( that I can see) that are text are whole numbers...1, 2, 3 etc. If you make those proper numbers, and not text, and again, set to 1 decimal place, it should all work for you

[protonLeah]

In the workbook posted, you have many values such as: 1.3-2.2 in column A. Those are not numbers. Are you typing the YVar values into C3 manually?

Aside: I used a dropdown in C3 using vals in col A and did not get any errors(?)

[FDibbins]

hmm and if what Ben says is the case - and you are looking for something within a range - you will need do split those up into their own cells. As Ben said, 1.1-0.2 is text, not a number, so excel will not associate it with any values/numbers.

Also, you have overlapping ranges...
1.1-0.2
1.1-2.0

Which value would you want for 1.1?
This is repeated in many other ranges

[MrShorty]

Here's what I did:

In column N, I put =ISNUMBER(A7). All of those that were "numbers" (0.2, 1.0, 2.0, etc.) came back with TRUE. So, it appears that your lookup values are a mix of text (x.x-x.x) and number (x.x). C3 is formatted so that it is always text. The text string "1.2" is not the same as the number 1.2, so the MATCH() function fails to find it, and returns N/A.

Fixing this depends on exactly what you want. Ben dropdown solution seems to work.

I tried formatting C3 as general, so that entries would show up as numbers or text strings depending on what was entered. 1.2-0.3 would be a text string, and it would find and row 18 just fine. 1.1 would be entered as a number, and it would find row 14 just fine.

I entered a formula in a helper row =IFERROR(TEXT(A7,"0.0"),A7), which converts everything in column A to text. Then copy this column -> paste special -> as values back into column A. Now everything in column A is text, and the lookup function finds everything just fine.

Acknowledging Ben and Ford's concern about ranges (which might mean looking at the different behavior offered by the 3rd argument of the MATCH() function), it seems to me that the key step at this point is really making sure that the lookup value in C3 has an EXACT match in column A.

[FDibbins]

I used this...
=INDEX($B$7:$J$139,MATCH(C3,$A$7:$A$139,0),MATCH(B3,$B$6:$J$6,0))
and as long as you convert those text numbers (1, 2, 3 etc) to real numbers (formatted to 1 decimal if needed), it all works well

[namy77]

Thank you guys- worked like a charm.

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[FDibbins]

Happy to help and thanks for the feedback

[namy77]

Thank you! Besides the format issue - I also discovered that due to a macro that I wrote to extract the N.N' column, it embeds hyperlink (to other worksheets) as well and it also seems to throw a monkey wrench into it...