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 500I 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 :)

Hi, I assume that when you say

I want to get top 5 itemyou 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.

IfItem 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 500Cell A2 has the formula =C2, then drag it down column A alongside the existing data.The formula

=VLOOKUP($D9,$A$2:$D$5,2,FALSE)where D9 contains the Item to be searched for, will now return the Product and=VLOOKUP($D9,$A$2:$D$5,4,FALSE)will return the Price.Regards

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History