Tool solving multiple indices on excel
Originally published: 14/06/2020 16:13
Publication number: ELQ-42712-1
View all versions & Certificate

Tool solving multiple indices on excel

Solves the problem of repeat hits in a VLOOKUP in excel

Solves the problem of repeat hits in a VLOOKUP in excel.
This is aimed at the intermediate and advanced excel user. Offers two solutions for the multiple match problem in excel. When using VLOOKUP for example with variable such as "APPLE, ORANGE, BANANA" there might be multiple occurrences. In this situation excel would return the first match and this might lead to mistakes in cased there are multiple match of the lookup term.
There are many ways of fixing this situation. The elegant formula involves the use of array formulas in excel (a bit more advanced) or to use INDEX and MATCH in a two stage process to identify the lines where the multiple matches occur.
This is an example with two tabs showing the 2 versions. The first tab uses a an array formula with INDEX and ROW. The second tab uses a two stage approach with MATCH and INDEX to show the output of all the matches for the lookup term.
Second option is easier to follow, update whereas the first tab uses a more integrated solution that takes up less space.
This is a tool and example that can then be used in complex excel business plans, business models to solve this problem

This Best Practice includes
1 Excel chart

Aram K offers you this Best Practice for free!

download for free

Add to bookmarks


0.0 / 5 (0 votes)

please wait...