Vlookup to left

Microsoft Excel 2003 (full)
January 21, 2010 at 21:39:57
Specs: Windows XP
Hi Tom,

I have data like this:
A1 B1 C1
A1 Product Item Price
A2 Pen 20 100
A3 Pencil 50 300
A4 Eraser 20 200
A5 Calculator 40 500

I have 4 sheets that contained same data type like above with different contain. I want to get top 5 item from 4 sheets. I already got the top 5 item using LARGE function, but i can't get the price and the product based on that item. I already try using VLOOKUP to get the price, but i get the same price because vlookup always read the first record.

Anyone could help me? i really need ASAP
thanks :)

See More: Vlookup to left

Report •

January 22, 2010 at 03:56:18

I assume that when you say I want to get top 5 item you are referring to the top 5 by 'Item', because you then go on to talk about getting the Price and the Product for them.

1. If Item is repeated, as it is in your example (Pen and Eraser both =20), then it is not possible to get the correct price and item.

2. If Item is unique and you have the top 5, Excel's VLOOKUP() function only does the lookup on the first column of a table or range of data and returns values from columns to the right.

3. To use your data for a VLOOKUP(), add a new column to the left of the Product column and create a copy of the item value.

	A		B		C	D
1	Item(copy)	Product		Item	Price
2	20		Pen		20	100
3	50		Pencil		50	300
4	20		Eraser		20	200
5	40		Calculator	40	500
Cell A2 has the formula =C2, then drag it down column A alongside the existing data.

The formula

where D9 contains the Item to be searched for, will now return the Product and
will return the Price.


Report •

January 22, 2010 at 05:32:58
Another option is to use the techniques described here:


Report •

Related Solutions

Ask Question