Question

Take any TWO different data ranges and use VLOOKUP to come up with a useful way to pull the information together. For example, if you have a price list and an order list (in separate locations), you may want to use VLOOKUP to determine how much an order costs (quantity x price). Use VLOOKUP to pull all of the information onto one excel sheet. Complete each of the following:

1-Show two separate accounting related data points

2-Effectively use VLOOKUP to pull information in one location in order to achieve a particular accounting objective (i.e. Total cost for an order)

3-State your accounting objective and how VLOOKUP helped you solve for the objective quicker and more effectively that doing it manually.

4- You must apply VLOOKUP on at least 10 lines of data.

Answer 1

Answer 1

The data in the first table is the quantity ordered in each order. The second table shows the price per unit fixed after negotiation for each order. (The order number is not in order and is shuffled.

Order Number	Quantity Ordered		Order Number	Unit price fixed for the order
101	10		107	8
102	40		110	7.5
103	45		103	9
104	50		104	7
105	15		102	10
106	20		106	10
107	35		101	9.5
108	55		109	7.5
109	100		108	6
110	10		105	10

Answer 2

Pulling the data from the second table to the third column of the first table shall resukt as below.

The formula used to fetch the first value is =VLOOKUP(B5,$F$4:$G$14,2)

where B5 is the cell containing the Common data in both table (Order number in this case)

$F$4:$G$14 is the second table (where we want to search for the data)

the 2 in the formula is the number of column where the required data is. (The price data is in second column of table)

You may refer the picture to understand the formula used

	Order Number	Quantity Ordered	Unit price fixed for the order
	101	10	8
	102	40	7.5
	103	45	9
	104	50	7
	105	15	10
	106	20	10
	107	35	9.5
	108	55	7.5
	109	100	6
	110	10	10

Answer 3

The task is to pull the unit price fixed after negotiation to the table containing the order quantity. As the order number is shuffled and it would be very hard to find the price fixed for each order, the function VLOOK came to help. Here, the price fixed for each order is pulled from the table and is made available in the required space. So, it would be easy to find the order amount or the relationship with order quantity and price as well.

The Data in both table contains 10 lines of sample taken randomly.