It works, thankyou. Unfortunatly I couldn't use the second option due to it
being an exsisting spreadsheet which I can't change.

However the issue I have is that it takes quite a while to find the answer,
is there a quiicker optio?

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xLookup.xls 14kb

http://cjoint.com/?hktaDRv717

In the file I've demonstrated 2 methods. The 1st (top) is based on your
setup and the 2nd (bottom) is how it "should" be done. Examine the formulas
used for both methods. Notice how much simpler the formula is for the 2nd
method!!!!!!

--
Biff
Microsoft Excel MVP


"KimC" wrote in message
...
Yes, but the Test & Test 2 are merged cells

"T. Valko" wrote:

Is this how your table is setup:


Model....Part Description....Yield
TEST.....Black Toner.........10,000
..............Cyan Toner...........11,000
..............Yellow Toner........12,000
..............Magenta Toner.....13,000
TEST2..Black Toner..........14,000
..............Cyan Toner...........15,000
..............Yellow Toner........16,000
..............Magenta Toner.....17,000


--
Biff
Microsoft Excel MVP


"KimC" wrote in message
...
I am trying to work out the formula to populate.

On Sheet 1 I have this

Model Black Toner Yeild
TEST
TEST 2

On Sheet 2 I have this

Model Part Description Yield
TEST Black Toner 10,000
Cyan Toner 11,000
Yellow Toner 12,000
Magenta Toner 13,000
TEST2 Black Toner 14,000
Cyan Toner 15,000
Yellow Toner 16,000
Magenta Toner 17,000

The First sheet one I have the correct formula should read

Model Black Toner Yeild
TEST 10,000
TEST 2 14,000

I'm thinking this need two lookups or an if and lookup - but i am very
stuck
on what it could be