We often need to query the data in the Excel document. For example, for stockholders, information data of some stocks are often collected in Excel. Generally, each stock symbol is a one-to-one correspondence with the name. For a regular forward query, if you want to query the stock name by code, you can find it quickly by using the VLOOKUP function to query the cell. But many times, you need to reverse lookup the code by name (because the name of the stock is better than the code memory), for example, to enter the stock name in H2, I2 shows the corresponding code and content (Figure 1), the opposite direction of the query, It can be achieved by the following methods.

Method 1: Reverse the positive direction of the reorganization data

As we all know, the VLOOKUP function input query data can only be located in the first column. In this example, the input query data is B column. To implement the query using the VLOOKUP function, we can use the data reorganization method to change the data column that needs to be entered into the first column. The data reorganization is realized by means of an IF function, and the formula "=VLOOKUP(H2,IF({1,0},B2:B10,A2:A10),2,0)" can be input in H2.

Formula explanation: Here we use the IF({1,0},B2:B10,A2:A10) function. The function means to use the array {1,0} to judge, and to return the value of B2:B10 through "1". "0" returns A2:A10 value, then the array is reorganized and B2:B10 becomes the first column. This corresponds to the query condition of the VLOOKUP function. When we enter the stock name in H2, the function will display the corresponding code in I2 (Figure 2).

Method 2: Use the LOOKUP Function Directly

Since you are using the input stock name to find the code, you can use LOOKUP (1,0/(condition), find array or area) to search. Enter the formula "=LOOKUP(1,0/(H2=B2:B10),A2:A10)" in cell I2.

Formula explanation: Here the condition is “H2=B2:B10”. The value of H2 is a set of logical values ​​obtained from B2:B10 (ie, the stock name column), and then divided by 0 to get the value of 0 and false value. The memory array is composed of the last one used as the query value to query in the memory array. Query the contents of the array as A2:A10 so that when the stock name is entered in H2, the corresponding code appears in I2 (Figure 3).

Similarly, for J2, if you also want to show the original corresponding D column (that is, the proportion of the total share capital), you can enter the formula "=LOOKUP(1,0/(H2=B2:B10),D2:D10)" in J2, So on and so forth. In this way, even if the original stock has a lot of data, just remember the name of the stock and enter the corresponding name to get the corresponding data (Figure 4).

Method 3: INDEX nested MATCH functions

Because here is the name entered in H2, then the code is displayed in I2. Therefore, it is actually necessary to reference the code data of column A in I2 according to the contents of H2. Therefore, we can use the INDEX function to refer to the formula. Enter the formula "=INDEX(A2:A10,MATCH(H2,B2:B10,))" in I2.

Formula explanation: Here we first use MATCH to find the relative position of H2 in the stock name column (B2:B10), and then use this position data as the index value of the INDEX function, and find the corresponding code display in column A. For example, when H2 enters a specific name such as "JiaXin Feihong", MATCH will position it to the 7th row (B7). At the same time, it will be indexed. INDEX will find the A7 data corresponding to B7 (ie, 300213) (Figure 5) .

The reference to the index can also use the OFFSET function. The OFFSET function performs the offset reference by using the line number obtained by the MATCH function. Also enter the function "=OFFSET(A2:A10,MATCH(H2,B2:B10,)-1,0,1,1)" at I2 (Figure 6).

As you can see from the above process description, the above query is actually just a conditional query technique in Excel. We can perform more queries based on our actual needs. For example, you can customize a query database, such as select the stock name to achieve accurate query of code and market value. Select H2, click "Data → Data Verification", switch to "Settings → Allow → Sequence" and select "B2:B10" from the source (Figure 7). Enter the formula "=INDEX(A2:G2,MATCH(H2,B2:B10,))" in I2 and J2 enter the formula "=INDEX(G2:G10,MATCH(H2,B2:B10,))", so that we H2 expands the drop-down list and selects the corresponding stock name. In I2 and J2, you can quickly display the required code and market value data (Figure 8).