How to use IndexMatch formula that ignores duplicates
Requirement:
To find a way around getting duplicates when running an IndexMatch formula. For example, it looks at column O (O10, for example and then searches through range D2:D115 to return whatever is in the adjacent column A once it finds it.
At the moment, there are some duplicate numbers in column O and so it's always returning the same result. Is there a way of having an extra bit in there somewhere to say "I've already found , so ignore that and find the next result"?
Solution:
Use this Array* formula:
=INDEX($A$2:$A$115,SMALL(IF($D$2:$D$115=O10,ROW($A$2:$A$115)-ROW($A$2)+1),ROWS(O$10:O10)))
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to index across multiple tabs |
| How to obtain validation list values from 3 columns based on INDEXMATCH of value offset from cell |
| How to use Index/Countif based on multiple factors including range |
| How to use SUMPRODUCT with INDEX MATCH multiple criteria |
| How to create VBA for index and match based on sheet criteria |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
