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

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

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

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

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

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

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 -