0 Multiple value VLOOKUP

Creating a multiple value VLOOKUP. The Microsoft Excel VLOOKUP() function is one of the most useful but confused spreadsheet functions. It allows you to look up a value in a range based on a value of an ‘index column’.

In the table below, you can see a standard product list:

If we wanted to retrieve the price of an item based on the Product ID, we would use the VLOOKUP() function, as seen here:

• The first parameter of the VLOOKUP() function is the value to look for. In our case it is the Product ID found in cell A12
• The second parameter is the range where we want to look for the value. In our case, it is the range containing the products table: A1:C6.
• The third parameter is the column from which the value will be returned. In our case it is the third column in the range, which is column C (The price column).
• The fourth column indicates whether the VLOOKUP() function will look for an exact match or if it will settle for a close match. In our case we selected FALSE which says – Exact match.

If you are already familiar with VLOOKUP then you know how powerful a function it is. And if you have just heard about it now – congratulations, you have become a better Excel user.

The problem with VLOOKUP()

VLOOKUP is very useful, but it also has a lot of limitations. One of the most obvious limitations is that you can only look up a value based on one parameter. Another obvious problem is that you can only find rows that exactly match the parameter. You can’t define logical rules such as ‘bigger than’, ‘smaller than’ and so on.
So, if we wanted to retrieve an employee from the following table. A deck hand that has 8 or more years of seniority, for example, we would not be able to use the VLOOKUP function.

Fortunately for us, there is a way to do perform this lookup:

This lookup formula is built from three parts:


First we create an array, that has 1 column and as many rows as our lookup table. This array will hold the value 1 for each row in the lookup table that has the value ‘Deck Hand’ in the Role column and holds a number greater or equal in the ‘Seniority’ column.
This is done by this expression:
(C1:C9="Deck Hand")*1*(D1:D9>=8)*1
When this expression is entered as a formula (you need to enter this formula with CTRL+SHIFT+ENTER because it is an array formula), it will return two arrays of 1s and 0s and multiply them. The result would be the array described above.
In the second step, we use the MATCH() function to find the location (the index) of the cell that contains 1 within the array.
We use the INDEX() function to retrieve the employee name from column B, based on the index of the row that we discovered above.


Summary


If you found it difficult to follow the above, the best approach to learning this technique is to give it a try in your own project. You will quickly see how powerful it is!
This formula template can be used to retrieve values based on any number of parameters and is a de-facto, multi-parameter VLOOKUP function.
What do you think? Can you use this type of formula in your work? Please share your thoughts with us in the comments.

0 Want To Learn Something New ---Blogging ?

Author : Smith Solace


Enjoy-----------------Simply this is the time to earn and earn for the future


with this opportunity you can earn online if you have internet connection or knowledge to earn from the internet if you can afford 1 to 2 hours of work on computer within a week.


so this time i have the best idea for all of the viewers who are interested to watch my blog and also wanted to earn from publishing the blogs.


Yes Now i have decided to teach the blog designing, earnings from the blog and also teaching s.e.o plug ins with the (search engine optimization) course this course will help you to earn from online personal business called blogging and publishing like the authors its totally a brand new concept will help you to earn money.


Eligibility criteria: any individual or person passed s.s.c or have knowledge of little bit english and know ms office or basics if not its okay able to learn this business so here is the syllabus for website development and blogging.



I just take 7 to 8 students for 1 batch of the Blogging. so don't worry.
for the fees and other details contact me 9601698816




syllabus
1) Basics of the internet
2) what is servers?
---how to register the space on server?
---what are the sites provide earnings?
3) what are the scripts?
4) how to get the website?
5) how to get the blog?
6) internet publishing and marketing
7) how to get advertisement possible?
8) search engine optimization
9) earnings and online money banking
10) protection from the spams and virus in the site?
11) safe publishing and advertisement licensing
12) earning from contera and Commission junction, click bank etc




this is the syllabus for the Blog Publishing just if you like you can contact me on my personal number 9601698816