Excel 2010: INDEX and COMPARISON have an advantage

Icon_Excel10_33x32Must 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

 

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

 

 

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 rowin which the Maximum value of second Column the function looks like this:

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

3_ COMPARISON

 

 

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)

4_nested

 

Transparency: This article may contain affiliate links. These lead directly to the provider. If a purchase is made through this, we receive a commission. There are no additional costs for you! These links help us to refinance the operation of win-tipps-tweaks.de.

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

Warning:
Using Registry Editor or its tips incorrectly can cause serious system problems that may require you to reinstall your operating system. Tampering with the registry files and using the tips is at your own risk.