:Can VLOOKUP reverse search?-Table skills免费ppt模版下载-道格办公

Can VLOOKUP reverse search?

Picture and text skills

When using the VLOOKUP function, the first parameter "lookup value" must be the first column of the selected area in the queried table to take effect, otherwise an error will occur.

For example, in this table, I want to query the quantity of the corresponding product through "product name", but the first column in the queried table is "quantity";

图一.png

Using =VLOOKUP(F2,$A$1:$C$6,1,0) directly in G2 will cause the "VLOOKUP" query to fail.

图片2.png

How to change the original structure without adjusting the column order in the table? The following introduces the "VLOOKUP" reverse search method, which involves IF

Function, we all know how to write the IF function =IF (test condition, true value, false value), the same way =IF({1,0},B:B,A:A), we get The result is the content of column B

The content is changed to the front of column A;

图片3.png

In cell G2, change the second item in the vlookup function to be queried to "IF({1,0},B:B,A:A)", and the third item "column sequence number" "Changed to "2",

Press Enter and the number corresponding to "table" will be obtained;

图片4.png

Move the mouse to the lower right corner of the cell, and when it forms a + shape, drag the cell downward to copy the formula, and the quantities corresponding to other product names are also obtained.

图五.png

Articles are uploaded by users and are for non-commercial browsing only. Posted by: Lomu, please indicate the source: https://www.daogebangong.com/en/articles/detail/VLOOKUP-ke-yi-ni-xiang-cha-zhao-ma.html