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.
A small example will be used to describe the two functions INDEX and MATCH , once independently of each other and once nested within each other.
1. INDEX () function
One use case for the INDEX function is as follows: You want to always have a specific cell value output from a specific matrix or the entire table .
= INDEX (matrix, row, column)
So the INDEX function for the cell from the fourth row and third column looks 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 an exact match, enter zero for the comparison type.
So, if you're looking for the row containing the maximum value of the second column , the function looks like this:
=VERGLEICH(MAX(B1:B8);B1:B8;0)
3. INDEX and MATCH functions nested
Things get really interesting when you nest the two functions . Only then does the advantage over VLOOKUP become apparent in some cases.
A new table was chosen for this example.
The goal is to retrieve the membership number of a specific person . Using a VLOOKUP function, the column for this would need to be to the right of the fourth column. This is not necessary here.
=INDEX(A2:D8;VERGLEICH("Tim";D2:D8;0);2)
