How to use the function VPR (VLOOKUP) to find and select the desired values from the list, we recently parsed. If you are not familiar with it yet, take a look here, take five minutes to save yourself a few hours later.
If you are familiar with VLOOKUP, then – in pursuit – you should deal with similar functions: INDEX (INDEX) и MORE EXPOSED (MATCH), the possession of which will greatly facilitate the life of any experienced user of Excel. Look at the following example:
It is necessary to determine the region of delivery by the article number typed in cell C16.
The problem is solved using two functions:
=INDEX(A1:G13;MORE EXPOSED(C16;D1: D13;0);2)
Function MORE EXPOSED searches in a column D1: D13 SKU value from cell C16. The last argument of the function 0 – means the search for an exact (not approximate) match. The function gives the ordinal number of the found value in the range, i.e. actually the line number where the required article is found.
Function INDEX selects from a range A1:G13 the value located at the intersection of the given string (the string number with the article is given by the function MORE EXPOSED) and a column (we need the region, i.e. the second column).
- Using the VLOOKUP function to look up and lookup values.
- An improved version of the VLOOKUP function
- Reusable VLOOKUP