INDEX and MATCH problems

[protocoledu]

Hi there,

been trying to do some INDEX and MATCH formulas to look up certain data, basically when I try and change the data range within the match part of the formula it keeps bringing random data up! Sometimes it would be bring the right value but if I went and changed the data range it would bring something else up!

This is my current forumla;

=INDEX('Data Sheet'!A:E,MATCH(B6,'Data Sheet'!A$1:A$6535,1)+7,3)

the results is 7 which is correct but if I was change the data range to;

=INDEX('Data Sheet'!A:E,MATCH(B6,'Data Sheet'!A$1:A$10535,1)+7,3)

it would bring up 11?!?! does anyone know of reason why the data would change! There is only 1 unique record it's looking up so it won't find more than to lookup?!?!

any help would be good!

[DonkeyOte]

In the MATCH you are using match_type of 1, should this in fact be 0 ?
(check out XL Help for more info)

[NBVC]

Try:


=INDEX('Data Sheet'!A:E,MATCH(B6,'Data Sheet'!A$1:A$10535,0)+7,3)

The 0 in the Match() function tells it to look for exact matches only.. the 1 makes it look for approximate match based on an ascending list...(i.e. looks for largest value that is less than or equal to lookup value). If the list is not in ascending order, you will get possibly strange results