What is wrong with this index/match function?

EdE

Board Regular
Joined
Apr 8, 2002
Messages
241
=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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Try if this works:

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

If so, the underlying format of A2:A189 must be text.
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top