What is wrong with this index/match function?

EdE · Apr 25th, 2002, 11:10 AM

=INDEX('sheet2'!$A$2:$H$189,MATCH(B15,'sheet2'!$A$2:$A$189,FALSE),1)

I know the value of sheet 1 b15 is in sheet 2 column A. It keeps giving me an NA# answer. I am obviously missing something small.

Thanks!

[ This Message was edited by: EdE on 2002-04-25 10:12 ]

Ricky Morris · Apr 25th, 2002, 11:15 AM

Make sure there are no trailing spacing in your source data or your lookup table. If there are, use the Trim function to clean it up.

EdE · Apr 25th, 2002, 11:19 AM

That was the first thing I thought of. I have used left/right justify to make sure I got rid of all of them. I have even cut the range to all numbers same length and still have the problem. Any more ideas?

Aladin Akyurek · Apr 25th, 2002, 11:22 AM

Quote:

On 2002-04-25 10:10, EdE wrote:
=INDEX('sheet2'!$A$2:$H$189,MATCH(B15,'sheet2'!$A$2:$A$189,FALSE),1)

I know the value of sheet 1 b15 is in sheet 2 column A. It keeps giving me an NA# answer. I am obviously missing something small.

Thanks!

[ This Message was edited by: EdE on 2002-04-25 10:12 ]

What are you trying to return from which column of 'sheet2'!$A$2:$H$189?

The formula as it stands should return a value (which is identical to the value in B15). If that is what you indend to do, then

=IF(ISNUMBER(MATCH(B15,'sheet2'!$A$2:$A$189,FALSE)),B15,"Not Found")

would suffice. If the value of B15 is in 'sheet2'!$A$2:$A$189 and you get #N/A, then they are in fact different--differently formatted or differently typed like with extraneous spaces.

Aladin

Ricky Morris · Apr 25th, 2002, 11:24 AM

If you're working with numbers, could it be one is text and the other is a value. Alsi, try copying the contents of B15, sheet 1, to any location in the match range on sheet2 to see if it finds it.

Aladin Akyurek · Apr 25th, 2002, 11:30 AM

Try if this works:

=MATCH(B15&"",'sheet2'!$A$2:$A$189,0)

If so, the underlying format of A2:A189 must be text.

EdE · Apr 25th, 2002, 11:42 AM

Ding ding ding we have a winner. Dont I feel stupid. Now, how do I get back formatted as a number? I did format cells, number, and no change. I can start as number, then change to text, and then back, but cant starting with text. I can click the individual cell, then enter and it changes, but I dont want to do 2000 cells individually.

Thanks!!

Aladin Akyurek · Apr 25th, 2002, 11:45 AM

Quote:

On 2002-04-25 10:42, EdE wrote:
Ding ding ding we have a winner. Dont I feel stupid. Now, how do I get back formatted as a number? I did format cells, number, and no change. I can start as number, then change to text, and then back, but cant starting with text. I can click the individual cell, then enter and it changes, but I dont want to do 2000 cells individually.

Thanks!!

Copy an unused empty cell, select the problem range, activate Edit|Paste Special >Add.

Aladin

Apr 25th, 2002, 11:15 AM	#2
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	Make sure there are no trailing spacing in your source data or your lookup table. If there are, use the Trim function to clean it up. __________________ It's never too late to learn something new. Ricky

Apr 25th, 2002, 11:24 AM	#5
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	If you're working with numbers, could it be one is text and the other is a value. Alsi, try copying the contents of B15, sheet 1, to any location in the match range on sheet2 to see if it finds it. __________________ It's never too late to learn something new. Ricky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 11:10 AM	#1
EdE Board Regular Join Date: Apr 2002 Location: A Mile High!! Posts: 230	=INDEX('sheet2'!$A$2:$H$189,MATCH(B15,'sheet2'!$A$2:$A$189,FALSE),1) I know the value of sheet 1 b15 is in sheet 2 column A. It keeps giving me an NA# answer. I am obviously missing something small. Thanks! [ This Message was edited by: EdE on 2002-04-25 10:12 ]

Apr 25th, 2002, 11:19 AM	#3
EdE Board Regular Join Date: Apr 2002 Location: A Mile High!! Posts: 230	That was the first thing I thought of. I have used left/right justify to make sure I got rid of all of them. I have even cut the range to all numbers same length and still have the problem. Any more ideas?

Apr 25th, 2002, 11:30 AM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Try if this works: =MATCH(B15&"",'sheet2'!$A$2:$A$189,0) If so, the underlying format of A2:A189 must be text.

Apr 25th, 2002, 11:42 AM	#7
EdE Board Regular Join Date: Apr 2002 Location: A Mile High!! Posts: 230	Ding ding ding we have a winner. Dont I feel stupid. Now, how do I get back formatted as a number? I did format cells, number, and no change. I can start as number, then change to text, and then back, but cant starting with text. I can click the individual cell, then enter and it changes, but I dont want to do 2000 cells individually. Thanks!!