# Vlookup

#### Amro-2011

##### New Member
Hello,

I have a table that contain different results for different items, i would like to use something like vlookup formula to show the results related to one of the items (as shown below), the problem if the items are not sorted based on the item name then i'll have gaps between the rows that resulted from the vlookup formula, any idea ?

Amro

Table 1

Material Amount Quantity

item 1 1000 500

item 1 500 250

item 2 100 2000

item 4 400 4000

item 2 350 7000

item 4 400 4000

item 1 650 325

Table 2

Choose Item: item 1

Amount Quantity

1000 500

500 250

650 325

Amro

Try the following

I assume that Material is in A1

make the following named areas

A1:A8 Material

F3 item

Put

Material in E5

Amount in F5

Quantity in G5

E6 =+MATCH(item,Material,0)

E6 =+MATCH(item,OFFSET(Material,E6,0),0)+E6

Copy E6 Down

F6 =+IF(E6=0,0,OFFSET(\$B\$1,E6,0))

G6 =+IF(E6=0,0,OFFSET(\$C\$1,E6,0))

Copy F6:G6 Down

voila

The table will have errors after the last value found, but they'll be easy to spot and always at the bottom

Hello Hui,

thanks alot for your prompt respond, i tried your formula's but i think it give the first value of the chosen item for all of the rows that hold the formula's.

what am trying to do is the following:

let's assume that the table 1 is a purchasing statement for the 3 items, and each row of table 1 represent a purchasing transaction, so in table 2 i want simply type down the name of the item and have the related row's of item 1 (amount and quantity).

in other words am doing filtering for item 1 using the excel formula (because i need to add more conditions to the formulas once i figure it out).

Thanks again

Amro

Have you tried setting the Table 1 up as a Table

Excel 2007 - Select range, Insert Table

eXCEL TO 2003 - Select range, Data Filter, Auto Filter

This allows automatic filtering of the results giving you want you want

E6 =+MATCH(item,Material,0)

E7 =+MATCH(item,OFFSET(Material,E6,0),0)+E6

Copy E7 Down

F6 =+IF(E6=0,0,OFFSET(\$B\$1,E6,0))

G6 =+IF(E6=0,0,OFFSET(\$C\$1,E6,0))

Copy F6:G6 Down

voila

Thanks alot Voila .

Amro