# INDEX/MATCH problem

#### sharrison

##### New Member
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### KSTWS

##### New Member
the index/matchformula should be like:

=INDEX(A1:B3,MATCH(A5,A1:A3,0),2)

#### sharrison

##### New Member
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.

#### KSTWS

##### New Member
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:

#### Stormseed

##### Banned
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 ?

#### sharrison

##### New Member
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)

#### sharrison

##### New Member
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.

#### Stormseed

##### Banned
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 ?

#### sharrison

##### New Member
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.

#### Stormseed

##### Banned
Can you send me that file ? Delete all your private or confidential data and send me that file.

Replies
7
Views
466
Replies
12
Views
884
Replies
1
Views
197
Replies
6
Views
633
Replies
4
Views
186

1,191,190
Messages
5,985,201
Members
439,947
Latest member
fabiannic

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

### Which adblocker are you using?

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

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