The VLOOKUP function is one that you really need to have in your
Excel tool kit. It allows you to lookup a value in a list - similar to
using a telephone book to find up a persons name in an address book get
their telephone number.
The syntax of the VLOOKUP formula is straightforward
=vlookup(lookup_value,table_array,column_index_number,[range_lookup])
where
1. lookup_value this is the value you want to lookup (for the phonebook example the persons name)
2. table_array this is the actual phone book or our list of names and telephone numbers
3. column_index_number The column number (starting with 1 for the left-most column of table-array) that contains the return value, so if we have telephone number is second column of the data then the value needs to be 2
4. range_lookup this is an optional value, but I would recommend always populating it.
A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
In looking up a telephone number, then we want an exact match so we use FALSE.
Lets apply the knowledge we have leant to looking for the type of Beannie hat that sold for the lowest price.
I have a list of transactional data of sales of Beannie Hats- a small extract can be seen below-
Price Item Ref
3.99 RedBean_3
4.79 Beannie_7
3.98 RedBean_3
Let's see how to use the VLOOKUP function combined with the MIN to look up the name of Beannie associated with the lowest selling price.
So we can start to build our formula using the VLOOKUP function. ( our prices are in Column C and Item Ref in column D in Excel)
=VLOOKUP(MIN(C2:C4),C3:D4,2,FALSE)
Lets talk through the formula logic.C2:C4 is the range that you want to find the lowest value, C2:D4 is the range of the data, we want to return the value in the second column of the range.
We can see the lowest price charged was for RedBean_3 at 3.98. Easy as that.
The syntax of the VLOOKUP formula is straightforward
=vlookup(lookup_value,table_array,column_index_number,[range_lookup])
where
1. lookup_value this is the value you want to lookup (for the phonebook example the persons name)
2. table_array this is the actual phone book or our list of names and telephone numbers
3. column_index_number The column number (starting with 1 for the left-most column of table-array) that contains the return value, so if we have telephone number is second column of the data then the value needs to be 2
4. range_lookup this is an optional value, but I would recommend always populating it.
A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
- TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one.
- FALSE searches for the exact value in the first column.
In looking up a telephone number, then we want an exact match so we use FALSE.
Lets apply the knowledge we have leant to looking for the type of Beannie hat that sold for the lowest price.
I have a list of transactional data of sales of Beannie Hats- a small extract can be seen below-
Price Item Ref
3.99 RedBean_3
4.79 Beannie_7
3.98 RedBean_3
Let's see how to use the VLOOKUP function combined with the MIN to look up the name of Beannie associated with the lowest selling price.
So we can start to build our formula using the VLOOKUP function. ( our prices are in Column C and Item Ref in column D in Excel)
=VLOOKUP(MIN(C2:C4),C3:D4,2,FALSE)
Lets talk through the formula logic.C2:C4 is the range that you want to find the lowest value, C2:D4 is the range of the data, we want to return the value in the second column of the range.
We can see the lowest price charged was for RedBean_3 at 3.98. Easy as that.
BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com
a site that shares Excel tips and tricks with it's enthusiastic
members. To join in the discussion, where users are working smarter and
faster with Excel sign up for the FREE newsletter and as a bonus receive
a FREE EBook- 50 Top Tips and Tricks.
Aucun commentaire:
Enregistrer un commentaire