• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.



New Member

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,


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

Try the following

I assume that Material is in A1

make the following named areas

A1:A8 Material

F3 item


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


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

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