syntax of VLOOKUP<\/strong> is:<\/li>\n<\/ol>\n\n\n\n=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])<\/code><\/pre>\n\n\n\n\nlookup_value:<\/strong> The value we are searching for.<\/li>\n\n\n\ntable_array:<\/strong> The range of cells where we are performing the search.<\/li>\n\n\n\ncol_index_num:<\/strong> The column number (1-based index)<\/strong> from which we want to retrieve the result.<\/li>\n\n\n\nrange_lookup:<\/strong> Optional.<\/strong> If TRUE (or omitted), an approximate match is performed; if FALSE, an exact match is performed.<\/li>\n<\/ul>\n\n\n\n\nA MID function<\/strong> is used to extract a specific number of characters from a text string, starting at a specified position. Its basic syntax is:<\/li>\n<\/ol>\n\n\n\n=MID(text, start_num, num_chars)<\/code><\/pre>\n\n\n\n\ntext:<\/strong> The text string from which we want to extract characters.<\/li>\n\n\n\nstart_num:<\/strong> The position of the first character we want to extract.<\/li>\n\n\n\nnum_chars:<\/strong> The number of characters that we are extracting.<\/li>\n<\/ul>\n\n\n\nCombining VLOOKUP and MID formula<\/h2>\n\n\n\n For our example, we will use the list of products, their codes, and sales records. Products will be Product A, B, and C<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nNow, let\u2019s suppose that the product code originally consisted of both numbers and letters and that we have one original code looking like this:<\/p>\n\n\n\n
ABC123ABC and GHI789GHI<\/strong><\/p>\n\n\n\nWe will store it in cells E2 and E3<\/strong>. Now there is a clear pattern here in which the shortened codes (number) begin from the fourth character<\/strong> in this extended code.<\/p>\n\n\n\nNow, if we want to find the sales of the original codes, we can do it with VLOOKUP and MID<\/strong>. In cell F2<\/strong> we will insert the following formula:<\/p>\n\n\n\n=VLOOKUP(MID(E2,4,3),A:C,3,0)<\/code><\/pre>\n\n\n\nBut we will be returned with an error:<\/p>\n\n\n\n <\/figure>\n\n\n\nThis formula uses the result in our MID formula as a lookup value<\/strong>. MID formula retrieves three numbers from our designated cell starting from the fourth character<\/strong>. The table array<\/strong> for our VLOOKUP are columns A:C<\/strong>, and we are searching for the sales values, located in the third column<\/strong>.<\/p>\n\n\n\nBut why do we get an error? The reason why this happens is that the MID function is a string function<\/strong> that always returns TEXT format<\/strong>. We are obviously looking for a number in our case. To change this, we need to add the VALUE formula<\/strong>, which has only one parameter – TEXT <\/strong>(this formula basically converts text to number). Our formula will now be:<\/p>\n\n\n\n=VLOOKUP(VALUE(MID(E2,4,3)),A:C,3,0)<\/code><\/pre>\n\n\n\nAnd we will get the expected result:<\/p>\n\n\n\n <\/figure>\n\n\n\nNow, this example is extremely simplified, but it shows a combination of formulas that can be used in more complex situations and datasets.<\/p>\n","protected":false},"excerpt":{"rendered":"
Excel formulas are made in a way that complicated things can seem easy. There is always an option to combine the formulas to…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[170],"yoast_head":"\n
Excel VLOOKUP and MID for Data Analysis<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n