Excel 2007 : Looking up the next row of a cell

[waihei]

Hello all,

[Cross posted from http://www.mrexcel.com/forum/showthread.php?t=441817]

I’ve been working on this for a whole week with no success.

I am trying to do a search function in my product catalogue.
I have all of my items of each category in separated worksheets. And I have this extra “Search” worksheet, so when I type in the Item number, it will look up all of the info of my product I am searching.

Here’s the table that carries my product info in the Worksheet BL (The table is named BL)
\1

Here’s the table in the “Search” worksheet:
\1

This is the formula I use to look up the gold Weight:
=VLOOKUP($A3, INDIRECT(MID($B3,1,2)), 3,0)
(note that I used the first 2 characters in the item no. to determine which worksheet to look up for the info)
Since each item may consist of more than 1 type of stones, so I have merged 4 rows for each item number so I can store up to 4 kinds of stones in each item.
So here’s my 2 questions:
1, is it possible for me to lookup a picture in the products worksheet and paste it into the cell in the search worksheet?

2, I do not have problem looking up the first set of stone using vlookup. But vlookup wont let me to look up the second type of stone and so forth.


Thanks in advance for any help =)

Wai.