INDEX/MATCH problem

sharrison

New Member
Joined
Jan 27, 2006
Messages
17
I am having a nightmare and don't know why!!

I have some data in a spreadsheet as below

898 243
998 268
1048 281

998 #N/A

The bottom cell containing the number 998 is the number that I want to find in the data. This value is taken from another sheet in the same workbook. This number you can clearly see is contained within the data.
The #N/A contains the INDEX/MATCH as below

=INDEX(S105:S107,MATCH(R108,R105:R107,0),1) where S105:S107 is the second column of information, R105:R107 is the first column of information and R108 is the cell containing the value 998.

As far as I am aware this should work and should return the value 268, however it continues to return the error. If I replace the link to the other spreadsheet which gives the 998 value with the value 998 then it finds the value 268.

Does anybody know why this is and how to get round it?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
If your data was in A1:B3 and your lookup(998) in A5
the index/matchformula should be like:

=INDEX(A1:B3,MATCH(A5,A1:A3,0),2)
 
Upvote 0
KSTWS thanks for the reply. That is basically what I have got but instead of including all of the data in the array and selecting column 2, I include the data I want to return and select column 1. When I alter the INDEX/MATCH to be in the same format as you posted the outcome is still the same.
 
Upvote 0
Maybe I read this wrong but I tried following:
=INDEX(S105:S107,MATCH(R109,R105:R107,0),1)
which gives me 268.

Is your Match lookup value in row 108 or 109?
 
Last edited:
Upvote 0
Are you sure that the number which you are looking up is a number or possibly text ? Do you receive #N/A or some other error ?
 
Upvote 0
Sorry for any confusion the data is set out as follows

R S
105 898 243
106 998 268
107 1048 281
108 998 #N/A

105-108 are the row numbers containing the data and R/S are the columns.

Therefore the INDEX/MATCH is =INDEX(S105:S107,MATCH(R108,R105:R107,0),1)
 
Upvote 0
Stormseed, I have tried changing the format of the data and the lookup value. If I just type in the look up value i.e. 998 then the INDEX/MATCH finds it, however the problem comes when the look up value is linked to another cell on the same worksheet or on another worksheet.
 
Upvote 0
Linking should not cause a problem as long as a value is returned - Do you receive an error on the cell to which this number is linked ?
 
Upvote 0
Nope. The formula that the lookup value contains i.e. the 998 value is as follows

=('Design Data'!F33*1000)-134 where the cell 'Design Data'!F33 contains the value 1.132 which is input by the user using data validation.
There are no errors shown except for the error returned by the INDEX/MATCH.
 
Upvote 0
Can you send me that file ? Delete all your private or confidential data and send me that file.

Send me a PM and I will reply to your email with my email address.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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