Excel 2010: INDEX and COMPARISON have an advantage

Must be in an excel spreadsheet different Split together compared, and targeted Values picked out that is in contrast to that VLOOKUP with a combination of INDEX- and COMPARE function very Comfortable possible. With a VLOOKUP, the columns must always be arranged in a specific way. The INDEX function can use all any Column combinations approach.

 

With a little one Example supposed to be the two functions INDEX and COMPARISON once independent from each other and once into each other nested to be discribed.

 

1. INDEX () function

 

A Use case for the INDEX function is the following: One would like from certain Matrix or the complete Table always one certain Cell value have spent.

= INDEX (matrix, row, column)

 

So the INDEX function looks for the cell from the fourth line and third column like this:

= INDEX (A1: D8; 4; 3)

 

 

2. COMPARE () function

The COMPARE function searches for a given value from a column and then outputs the line in which the value is located.

= COMPARE (search criterion; search matrix; comparison type)

For a exact match is given for the comparison type a zero .

So if you look for the Zeilein which the Maximum value is second Column the function looks like this:

=VERGLEICH(MAX(B1:B8);B1:B8;0)

 

 

3. INDEX and MATCH functions nested

wirklich interesting it will actually be when you have the two Features into each other nested. Even then, the advantage for some cases compared to the VLOOKUP can only be seen.

For this Example became a new Table .

It should now be the Mitgliedsnumm is certain Person are issued. For a VLOOKUP, the column should be to the right of the fourth column. This is not necessary here.

=INDEX(A2:D8;VERGLEICH("Tim";D2:D8;0);2)

 

___________________________________________________
This tip comes from www.win-tipps-tweaks.de
© Copyright Michael Hille