Hello, I am Han Jeong-hee. This tutorial is a VLOOKUP function, Basic usage of the HLOOKUP function Let me explain First, I prepared a simple example. Here is the consumable purchase status table In this table, the unit price corresponding to the corresponding product name I'm trying to input manually It is very inconvenient when there is a lot of data. So I made the price tag on the right in advance I made two price tags I will explain again after a while Function to find the unit price that matches the product name in the unit price list Usually I'm going to explain now in the find reference area Using the VLOOKUP and HLOOKUP functions It ’s very easy to come by. First, in some cases, use the VLOOKUP function When to use HLOOKUP For a moment, take a look at the price tag here. If you look at the direction on the price tag, What is the product name or unit price here? It is input vertically in the column direction. At this time, after vertical V When trying to find a value from such a table Just use the VLOOKUP function Then, shall we look at the ticket on the right? In the case of the table on the right, the product name and unit price The field names are entered in the row direction, ie horizontally. I set the standard like this When it is input horizontally like this Using H, using a function called HLOOKUP You can find the value Now VLOOKUP, HLOOKUP according to the table shape In some cases VLOOKUP and in some cases HLOOKUP I can tell Personally, I'm not good at making row-oriented tables. Usually when designing a table like this I usually make a lot of column-oriented tables like this. That ’s why there are a lot more things to fear
Now, let's introduce how to use arguments using functions I'll give you First select the unit cell <E4> you want to get the result from What about just typing vl? VLOOKUP function It shows right in the function library. Then double click on the VLOOKUP function Even if you don't enter a function, it is automatically entered. In this state, press the insert function button (fx) I will introduce you by using a wizard Let's open the Function Wizard and look at the arguments. Four arguments, first argument Lookup_value This is the value you are looking for. The value you are looking for right now is the product name? Condition corresponds to article name So Lookup_value is the condition you want to find. So we specify the value of the postit You can type 'post-it' directly, We're only going to get the first unit price right now The rest is to copy the formula and get the result. So it is correct to refer to the cell The second Table_array argument specifies the range of the unit price table When specifying the price range, No. is in the first column. But the value you are trying to find, that is, the condition becomes the product name. So the first column in this Table_array is unconditionally So that the condition value, the value you are looking for, is in the first column You have to specify the range. Of course, even if you include the product name and price range here, But the result is the same whether you include it or not We're assigning a range to the values we're looking for. Re-specify, and this range is never referenced Press function key <F4> to refer to it like this. Then the third argument Col_index_num You are asked to enter the Column Number. That's where I just scoped the table. Then in the VLOOKUP function Index_num is assigned within this range. This first column is 1, the second column is 2, If there is another row, 3, 4 like this I already set the rule to use the index number. So after finding the post-it in the first column What is the actual value you are trying to bring Is it unity? So, input the second column 2. Enter 2 in Col_index_num Shall we look at the help for the fourth argument, Range_lookup? Click and look at the help FALSE to find an exact match To find similar matching values It says TRUE or omit. FALSE can be written as 0 instead. TRUE can be 1 or omitted. But the value I am trying to find here is post-it It becomes text data. If they match similarly I will introduce you again in the next lecture In the case of similar matches, Above a certain value Below a certain value Exceeds a certain value and is less than a certain value In this way, where the value of the comparative level goes Usually you can find similar values In case of text like now, more than post it, less than post it That doesn't make any sense. So, in the case of text Only if you find the exact exact value Is applicable So I'm going to enter 0 in Range_lookup The first argument Lookup_value is a condition, The second argument is a conditional search Specify a table with data Then Col_index_num is in the table scoping area The first column has index numbers 1, 2, and 3 It is given like this. Since the corresponding unit price is in column 2, enter 2 The character is outside the exact value of Range_lookup unconditionally. I said that it will not be established That's why I input 0 Click OK and take a look at the results The unit price was obtained very easily and quickly. No matter how much you try to come, You can get the unit price in an instant by just copying the formula. So if you need this function Must be cooked and used The data in error The post-it is spaced like this. A typo that doesn't fit with the table like now In the case of such spaced data, I get this error You can fix the data If you stick the post it like this You can confirm that the value is returned normally. I just introduced the Vlookup function. The usage of the HLOOKUP function is the same. I'll do it one more time HLOOKUP, I will open the Function Wizard The value you're trying to find for the first argument, is it postit? Table_array is entered right in the row direction. Specify this range Then, the first row of this range The second row will be the second row. The Row_index_num argument must be 2 And the Table_array part is never referenced Don't forget Enter 2 and try to find the value Post-it, right? That's exactly 0. HLOOKUP function, VLOOKUP function, how to use the same? The only difference is Col_index_num, Row_index_num Press [OK] and copy the formula The unit price was so easy.
