Above all we will understand INDEX function and MATCH function
INDEX(data, the N that N) expresses to access is occupied
For example, we input a formula in E2 unit case:
=INDEX(E:E, 5) , it expresses to return E to arrange the 5th medium data, it is tank namely
MATCH function
MATCH(searchs a value, search area, search means)
For example, we input formula in H3 unit case:
=MATCH(G3, b:B, 0) , express to search the value of G3 unit case, the a fewth is in B row, the last parameter is 0 express to be searched accurately
That is to say Cheng bites gold to list medium data to be in the 5th in B.
Collocation of INDEX+MATCH of these two function rises inapproachable, search match super and convenient, for example, we need to search its location according to the hero now
So the a fewth when we find out heroic correspondence to be listed in the hero above all, use MATCH function so:
Xiaoqiao is in the 7th, cheng bites gold to be in the 5th, next it is OK that function of our reoccupy INDEX takes us to need to list these corresponding serial numbers, the result that we should get now is fixed position, so, we use:
=INDEX(E:E, MATCH(G2, b:B, 0) )
Use this formula so, have an all-purpose formula:
This is used fortunately than VLOOKUP function, because of VLOOKUP function can from left search right, and the limitation that this function did not control, if we want to search number according to the hero now, cannot come true with VLOOKUP, using Index+match also is relaxed implementation, the input is formulary:
It is first-rate with, oneself start work try ~ welcomes to leave a message discussion ~
