In this example, we need to update commission percentage (%) based on sales amount figure that we have already updated above. In this method, Google Sheets VLOOKUP formula first searches for an exact match of search_key, and if an exact match is not found then, formula looks for a value that is the closest match or next smaller value to search_key. Google Sheets VLOOKUP function searches the search_key based on approximate or closest match type. Google Sheets VLOOKUP for approximate or closest match When the price of each Brand in each region is updated, the sales amount is also updated because it is a product of quantity and price as shown below When we copy down the above Google Sheets VLOOKUP formula to other rows in Price column, it picks the price of each unique brand from Price list range. We need to make the range absolute by inserting $ sign with column and row references so that range reference does not change when we copy down the formula to other rows. Google Sheets VLOOKUP for the exact match of unique Brands is as follows When the price of each brand in each region is updated, sales amount is also updated as sales amount is the product of Quantity and Price of each carton. As per above example, we have a price list of each unique brand in data range H1:I10. Suppose we have a sales data set of cigarette brands in various regions and we want to update price, sales amount and commission percentage based on sales amounts brackets.įirst of all, we need to update the price of each brand in the main data sheet from Price list range of each brand based on an exact match. Now we will discuss some examples of Google Sheets VLOOKUP function to have more clarity on how to use this function. A question mark (?) is used to match any single character, but an asterisk (*) is used to match any sequence of characters.Įxamples of Google Sheets VLOOKUP function
0 Comments
Leave a Reply. |