how to vlookup in excel and examples

In this articles how to vlookup in excel and Examples , I tried to talk about why Excel is often used and how practical and useful the problem-solving approach it offers and how to vlookup in excel. I think the VLOOKUP function is one of the essential features that make Excel so valuable. 



how to vlookup in excel
how to vlookup in excel

how to vlookup in excel and through an example

 

We can call whatever we want from the tables prepared before in variable reference-based search operations like a tiny database processing software. How Does? Let's first define the VLOOKUP, then understand it in the examples and dig a little deeper.

 

Description: VLOOKUP is an Excel function that works with left-to-right processing logic and enables us to call the column we want by referring to the leftmost value from a previously prepared table.

 

Definitions can sometimes be insufficient to understand things we don't know what happened, but they become more transparent when read again after understanding what happened. So let's try to understand through an example.how to vlookup in excel?

Important Link: Rules for using shortcut of keyboard | (F1 to F12)

Example: We want to buy a car, but there are so many options that we have a hard time deciding. The vehicle we will purchase must be prestigious (brand), high horsepower, and low fuel consumption. Also, the price and annual tax should be low (low engine). Let's make an imaginary chart of car brands, fees, and engine and horsepower. Let's imagine all brands and models so that they do not enter the advertisement.

 

Since a brand can have more than one model, we dreamed of 5 brands and ten models. Now, we can call any feature of the models using VLOOKUP anywhere in the table. How? and how to vlookup in excelFirst, let's take a look at how VLOOKUP works and also VLOOKUP Microsoft site Examples.


HowDoes Excel VLOOKUP Work?

 

VLOOKUP function works by taking four parameters. These:

 

Lookup_value: The primary reference value we are looking for (must be at the far left in the table)

 

Table_array: The table we will search for

 

Col-index_num: How many columns we want the value.

 

Range_lookup: Whether the exact value we are looking for, or the closest result is requested

Let's continue with the example. We want to learn the horsepower of the Model D from the table of cars above. Then:



how to vlookup in excel
how to vlookup in excel


There are some points we need to pay attention to here. First of all, it is beneficial to fix the table boundaries that we have defined in the table_array field because if we pull this lookup function to use it for many areas, our reference table should remain the same. You can also check out my article on fixing reference cells in Excel for more detailed information and examples. A practical tool for improving a table may be to name the table. We reviewed this cell-related experiment in the Excel Random Number Generation Methods article. We will see how we will apply it to the table soon.

Important Link: Structured Data | Structured Data Testing Tool

The second important point is that we would like to see lookup values ​​that exactly correspond to what we are looking for in almost all applications. If it is not in the table, the closest result will not be the result we are looking for. For this reason, it would be healthier to use the range_lookup value as false mostly.


 how to vlookup in excel and examples


Let's examine how useful the Vlookup function is with an example containing more detailed and nested Vlookup queries.

 

Excel VLOOKUP Examples

 

Detailed Example: Let's develop an excel file that allows us to select a vehicle using the fictional car models and features we used in the previous example. In this table, each car segment should be taken into account as a factor in our decision-making rate.



how to vlookup in excel
how to vlookup in excel

 

- Brand (let's consider some brands on the list as more prestigious)

 

- Horsepower (the more horsepower the vehicle has, the better)

 

- Engine power (less engine power will allow us to pay less motor vehicle taxes)

 

- Fuel Consumption Value (We want the fuel consumption values ​​of our vehicle to below)

 

- Price (The low price of the car is one of our most important criteria)

 

All these values ​​should have specific weight percentages that will make an impact on our selection. For example, while the vehicle's price is 40% important to us when making a decision, the horsepower height maybe 10% important.

Important Link: Newest technology | next generation g a d g e t 2021

For this, let's create a feature importance index.how to vlookup in excel

 

So which brands are more prestigious? For this, we need to write a prestige value for each brand. There were five brands in our example.

 

Similarly, let's make a score for 1200, 1600, 1800, and 2000cc engines. The lower the motor, the higher the score should be.

 

We can produce two different approaches for our remaining criteria. One of them is to make payroll for intervals, and the other is to produce payroll by ranking. Since we haven't done a tutorial for sorting yet, let's create a payroll over the ranges, so let's sample the scenario where the range_lookup value is real.

Important Link: What is the secret codes of an Android phone | you never know?

So how do we calculate the score for each vehicle? The formula will look like this:

 

(10% x Brand Prestige Points) + (10% x Horsepower Points) + (20% x Engine PowerPoints) + (20% x Fuel Consumption Value Points) + (40% x Vehicle Price Points)



how to vlookup in excel
how to vlookup in excel

Let's examine our existing excel table as a whole before going through the solution.

 

We came to the solution to the problem. First of all, we can start by calling the brand prestige score with vlookup.

 

Brand Score step 1: Let's call the brand of Model A in cell B15:

 

= VLOOKUP (A15; $ A $ 2: $ F $ 11; 2; FALSE)

 

Brand Score 2nd step: Let's find the brand's score we are calling from the prestige values ​​table.

= VLOOKUP (VLOOKUP (A15; $ A $ 2: $ F $ 11; 2; FALSE); $ H $ 10: $ I $ 14; 2; FALSE)

 

It may seem a bit complicated, but we need to find the model's brand and then the relevant brand's score using two nested vlookups. These types of applications will also appear frequently in real business operations.

 

Note: If you get an N / A error when you do this, your values ​​in the model columns do not match exactly and cannot be found by vlookup. For example, Brand 1 and Brand1 may produce a similar error due to the gap between them.

Important Link: What are the 9+ tricks know about smartphones

If we found our brand scores, let's celebrate with a visual:

 

Indeed, Excel brought me the relevant point value by using first the brand of the vehicle, which I know the model of, and then the brand, thanks to vlookup. Moreover, we did not write even 1 line of code except using a function.

 

Let's come to the horsepower. An additional problem we have to solve here is the bar. For example, Model H has 94 horsepower, but there is no value for 94 horsepower in horsepower scoring.

 

= VLOOKUP (VLOOKUP (A22; $ A $ 2: $ F $ 11; 3; FALSE); K4: L13; 2; FALSE)

 

At this point, we will have the opportunity to make the range_lookup value of the vlookup function real because if we are looking for another numerical equivalent of a numeric value (the numerical score of 94), we should be able to use 90. Let's update the outside vlookup input_range value to TRUE in the formula we wrote above.

 

Note: You must also update the horsepower score table in ascending order for this process to work.

 

Bonus: We mentioned that we could name the tables. For example, let's briefly name the field BGP where we store our values ​​in the horsepower scoring table. For this process, let's activate the cells between K4: L13 by selecting them and write BGP in the field that writes the cell/table value at the top left. Now you can see that the table's name is written in this field every time we select this field, and even better, it will be enough to write bgp when using this table in our formulas.



how to vlookup in excel
how to vlookup in excel

Important Link: 6 tricks how to use best smartphone usefully

Let's return to our scoreboard and calculate the remaining horsepower points.

 

Formula (for cell C15): = VLOOKUP (VLOOKUP ($ A15; $ A $ 2: $ F $ 11; 3; FALSE); bgp; 2; TRUE)

When we extend this formula for the other fields (C16: C24):

 

The calculation of the engine power score will be as in the brand score calculation since we already have the exact payoff values:

 

Formula for cell D15: = VLOOKUP (VLOOKUP ($ A15; $ A $ 2: $ F $ 11; 4; FALSE); $ H $ 17: $ I $ 20; 2; FALSE)

 

Similarly, fuel consumption will be the same as the horsepower calculation since it is not the closest value:

 

Formula for cell E15: = VLOOKUP (VLOOKUP ($ A15; $ A $ 2: $ F $ 11; 5; FALSE); $ K $ 17: $ L $ 21; 2; TRUE)

 

We can write the price consumption formula similarly.

 

For cell F15: = VLOOKUP (VLOOKUP ($ A15; $ A $ 2: $ F $ 11; 6; FALSE); $ K $ 25: $ L $ 30; 2; TRUE)

 

We came to the tail of swimming. Let's see who will be the winner when we collect the points we calculate by proportioning the feature importance index rates we give?

 

Total formula for cell G15: = $ I $ 3 * B15 + $ I $ 4 * C15 + $ I $ 5 * D15 + $ I $ 6 * E15 + $ I $ 7 * F15

Important Link: How to create a mobile app and does it cost earn?

Model G was the answer to our question with a price of 68,000 TL for 80 horsepower. Of course, the reason for this was that we gave importance to the price at the rate of 40%.

 

Did you give up saving by making a change of decision, solved question about how to vlookup in excel? and are you afraid of making all these calculations from scratch? Thanks to Excel, all you have to do is update the values ​​in the importance index. Let Excel take care of the rest.

(  gggggggggggg )

 

Reactions

Post a Comment

0 Comments