index,match,match on un-sorted data
 

I am using =index,match,match to extract data from an array which I
don't control. It worked fine whilst the entries in the left-most
column were sorted (descending) but with new lines being added by other
users, the formula is now returning garbage. I've tried a number of
methods but nothing that doesn't involve numerous steps seems to work
consistently.

Any idea sout there?


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=470386

check the third argument of the match function. i assume you want to match
your data exactly - it should be set to 0. the match function can match data
exactly or approximately, but it is determined by the third argument of the
function.

if you want to match data approximately, your list will need to be sorted.


"Brisbane Rob" wrote:


I am using =index,match,match to extract data from an array which I
don't control. It worked fine whilst the entries in the left-most
column were sorted (descending) but with new lines being added by other
users, the formula is now returning garbage. I've tried a number of
methods but nothing that doesn't involve numerous steps seems to work
consistently.

Any idea sout there?


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=470386

If your data is not sorted, I would guess that you want an exact match.

Did your =match() expressions look like:

=match(a1,sheet2!a:a,0)
(that last 0 says exact match)

Debra Dalgleish has lots of tips at:
http://www.contextures.com/xlFunctions03.html

Brisbane Rob wrote:

I am using =index,match,match to extract data from an array which I
don't control. It worked fine whilst the entries in the left-most
column were sorted (descending) but with new lines being added by other
users, the formula is now returning garbage. I've tried a number of
methods but nothing that doesn't involve numerous steps seems to work
consistently.

Any idea sout there?

--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=470386

--

Dave Peterson

Thanks - I didn't know about the 0


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=470386