Thanks:  0
Likes:  0

# Thread: What is wrong with this index/match function?

1. =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 ]

2. 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.

3. 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?

4. 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

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.

5. 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.

6. Try if this works:

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

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

7. 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!!

8. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•