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 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.
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 excel, First, 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 |
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.
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 |
- 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 |
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 |
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 )
Reviewed by Anonymous
on
February 28, 2021
Rating:
No comments: