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?

 

[Guest]

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.

 

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

Thanks - I didn't know about the 0