Vlookup

[Amro-2011]

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 ?


thanks in advance,


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

 

[Hui]

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

 

[Amro-2011]

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

 

[Hui]

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


ps: I made a typo above it should have read


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

 

[Amro-2011]

Thanks alot Voila .


Amro