| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match & Index
I have an Index and Match formula which is supposed to match a number
formatted as text. I keep getting #N/A in the match cell. If I choose the "convert to number" selection in the error checking option, the formula works. I have tried to change the property to numbers and still receive an error. I have used this formula in the past without issue. -- Tekhnikos |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match & Index
post your formula
"Tekhnikos" wrote: I have an Index and Match formula which is supposed to match a number formatted as text. I keep getting #N/A in the match cell. If I choose the "convert to number" selection in the error checking option, the formula works. I have tried to change the property to numbers and still receive an error. I have used this formula in the past without issue. -- Tekhnikos |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match & Index
=INDEX(Selections!A2:E441,MATCH(Data!A2,Selections !A2:A441,0),4)
The format of SelectionsA2 is text, the format of Data!a2 is text -- Tekhnikos "Mike H" wrote: post your formula "Tekhnikos" wrote: I have an Index and Match formula which is supposed to match a number formatted as text. I keep getting #N/A in the match cell. If I choose the "convert to number" selection in the error checking option, the formula works. I have tried to change the property to numbers and still receive an error. I have used this formula in the past without issue. -- Tekhnikos |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match & Index
I also tried using VLookup function as well - I have the same error. The
cell that is supposed to be matched is based a combo box selection. -- Tekhnikos "Tekhnikos" wrote: =INDEX(Selections!A2:E441,MATCH(Data!A2,Selections !A2:A441,0),4) The format of SelectionsA2 is text, the format of Data!a2 is text -- Tekhnikos "Mike H" wrote: post your formula "Tekhnikos" wrote: I have an Index and Match formula which is supposed to match a number formatted as text. I keep getting #N/A in the match cell. If I choose the "convert to number" selection in the error checking option, the formula works. I have tried to change the property to numbers and still receive an error. I have used this formula in the past without issue. -- Tekhnikos |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match & Index
Hi,
the formula works fine text or numbers in the lookup value or range so i suspect your text values may not be what you think they are, erronious spaces maybe I'd use this to do the same thing =VLOOKUP(TRIM(Data!A2),Selections!A2:D441,4,FALSE) Note that in you formula column e is referenced but not used. Mike "Tekhnikos" wrote: =INDEX(Selections!A2:E441,MATCH(Data!A2,Selections !A2:A441,0),4) The format of SelectionsA2 is text, the format of Data!a2 is text -- Tekhnikos "Mike H" wrote: post your formula "Tekhnikos" wrote: I have an Index and Match formula which is supposed to match a number formatted as text. I keep getting #N/A in the match cell. If I choose the "convert to number" selection in the error checking option, the formula works. I have tried to change the property to numbers and still receive an error. I have used this formula in the past without issue. -- Tekhnikos |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match & Index
What appears to be happening is a hidden value of some sort is added to the
cell from the combo box selection. The LinkedCell from the combo is used for the lookup.. any ideas on how to stop this from happening? -- Tekhnikos "Mike H" wrote: Hi, the formula works fine text or numbers in the lookup value or range so i suspect your text values may not be what you think they are, erronious spaces maybe I'd use this to do the same thing =VLOOKUP(TRIM(Data!A2),Selections!A2:D441,4,FALSE) Note that in you formula column e is referenced but not used. Mike "Tekhnikos" wrote: =INDEX(Selections!A2:E441,MATCH(Data!A2,Selections !A2:A441,0),4) The format of SelectionsA2 is text, the format of Data!a2 is text -- Tekhnikos "Mike H" wrote: post your formula "Tekhnikos" wrote: I have an Index and Match formula which is supposed to match a number formatted as text. I keep getting #N/A in the match cell. If I choose the "convert to number" selection in the error checking option, the formula works. I have tried to change the property to numbers and still receive an error. I have used this formula in the past without issue. -- Tekhnikos |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match & Index
You could use as your lookup value:
LEFT(Data!A2,LEN(Data!A2)-1) if that extra character is at the end of Data!A2 (or use RIGHT if it is at the beginning). Hope this helps. Pete On Jan 16, 11:16*pm, Tekhnikos wrote: What appears to be happening is a hidden value of some sort is added to the cell from the combo box selection. *The LinkedCell from the combo is used for the lookup.. any ideas on how to stop this from happening? -- Tekhnikos "Mike H" wrote: Hi, the formula works fine text or numbers in the lookup value or range so i suspect your text values may not be what you think they are, erronious spaces maybe I'd use this to do the same thing =VLOOKUP(TRIM(Data!A2),Selections!A2:D441,4,FALSE) Note that in you formula column e is referenced but not used. Mike "Tekhnikos" wrote: =INDEX(Selections!A2:E441,MATCH(Data!A2,Selections !A2:A441,0),4) The format of SelectionsA2 is text, the format of Data!a2 is text -- Tekhnikos "Mike H" wrote: post your formula "Tekhnikos" wrote: I have an Index and Match formula which is supposed to match a number formatted as text. *I keep getting #N/A in the match cell. *If I choose the "convert to number" selection in the error checking option, the formula works. *I have tried to change the property to numbers and still receive an error. *I have used this formula in the past without issue. -- Tekhnikos- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
| index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
Hybrid Mode
