index,match,match on un-sorted data

[Brisbane Rob]

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?

[JMB]

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

[Dave Peterson]

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

[Brisbane Rob]

Thanks - I didn't know about the 0