How Excel looks at the inverse of the VLOOKUP function to the left lookup formula

As you know, in Excel you can use the VLOOKUP function to find data for the corresponding rows in other columns based on one of the first columns in the range. For example, to find the name that corresponds to the "on duty date" in the following figure, you can use the formula: Duanwu

=vlookup (a6,a1:d7,2,0)

But the VLOOKUP function can only look to the right, if you want to look to the left to find the value, you use the index function and the MATCH function combination formula. For example, to find the name of section "section" in the previous example, which is found in column 2nd by the data in column 4th, you can use the following formula:

=index (A2:d7,match (D6, $D $: $D $7,0), 2)

Or:

=index (B2:b7,match (c6,c2:c7,0))

Or use the lookup function:

=lookup (1,0/(D2:D7=D6), b2:b7)

You can also still use the VLOOKUP function:

=vlookup (d6,if ({1,0},d2:d7,b2:b7), 2,)

All of the above formulas return to "Zhou Bo Pass". The last formula, the second parameter "IF ({1,0},d2:d7,b2:b7)" of the VLOOKUP function, is returned by a two-dimensional array, where D2:D7 is the first column and B2:b7 is the second column. "If ({1,0},d2:d7,b2:b7)" can be considered "{if (1,D2:D7), if (0,,B2:B7)}". You can also return such an array with the text function, such as changing the second argument of the formula to "TEXT {1,-1},d2:d7&"; &B2:B7) ":

=vlookup (D6,text ({1,-1},d2:d7& ";") &B2:B7), 2,)

After this formula is finished, you need to press Ctrl+shift+enter to finish.

To find the "Duty date" for section "section", that is, to find it in column 1th by the 4th column of data, simply change the last argument "2" to "1" in the first formula:

=index (A2:d7,match (D6, $D $: $D $7,0), 1)

Change the "B2:b7" in the latter several formulas to "A2:A7":

=index (A2:a7,match (c6,c2:c7,0))

=lookup (1,0/(D2:D7=D6), A2:A7)

=vlookup (d6,if ({1,0},d2:d7,a2:a7), 2,)

Use the following formula to place name in any position to the left of the section column, and the formula will return the correct result.

=index ($A $ $D $7,match (D6, $D $ $D $7,0), MATCH ("name", $A $: $D $1,0))

Note : More wonderful tutorials Please pay attention to the triple computer tutorial section, triple Computer office group: 189034526 welcome you to join