If or Lookup or Something else

[KimC]

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

 

[T. Valko]

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

 

[KimC]

Yes, but the Test & Test 2 are merged cells

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

 

[T. Valko]

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!!!!!!

 

[KimC]

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]

it takes quite a while to find the answer,

is there a quiicker optio[n]?

Well, that's because you need a fairly complex formula to work with that
setup. That's why I showed you the other method.

Does every model have the same number of part descriptions? In your sample
data each model has 4 descriptions.

 

[KimC]

No they range, sometimes they have 2 or sometimes they have 6+

I've been entering it into the spreadsheet and now some work and some don't
- comes up with #N/A error, I have made sure the text in the two different
cells match.

it takes quite a while to find the answer,
is there a quiicker optio[n]?

Well, that's because you need a fairly complex formula to work with that
setup. That's why I showed you the other method.

Does every model have the same number of part descriptions? In your sample
data each model has 4 descriptions.

--
Biff
Microsoft Excel MVP

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]

Here's an alternative formula based on the setup of the sample file but I
don't think this is any better than the other formulas I've suggested. It
may even be worse since it's now volatile.

=INDEX(C$2:C$9,MATCH(E2,A$2:A$9,0)-1+MATCH(F$1,B$9:OFFSET(A$2,MATCH(E2,A$2:A$9,0)-1,1),0))


--
Biff
Microsoft Excel MVP

No they range, sometimes they have 2 or sometimes they have 6+

I've been entering it into the spreadsheet and now some work and some
don't
- comes up with #N/A error, I have made sure the text in the two different
cells match.

it takes quite a while to find the answer,
is there a quiicker optio[n]?

Well, that's because you need a fairly complex formula to work with that
setup. That's why I showed you the other method.

Does every model have the same number of part descriptions? In your
sample
data each model has 4 descriptions.

--
Biff
Microsoft Excel MVP

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?

:

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


Yes, but the Test & Test 2 are merged cells

:

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


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

 

[T. Valko]

Eh...

Disregard that last formula. After some testing it doesn't work properly.

--
Biff
Microsoft Excel MVP

Here's an alternative formula based on the setup of the sample file but I
don't think this is any better than the other formulas I've suggested. It
may even be worse since it's now volatile.

=INDEX(C$2:C$9,MATCH(E2,A$2:A$9,0)-1+MATCH(F$1,B$9:OFFSET(A$2,MATCH(E2,A$2:A$9,0)-1,1),0))


--
Biff
Microsoft Excel MVP

No they range, sometimes they have 2 or sometimes they have 6+

I've been entering it into the spreadsheet and now some work and some
don't
- comes up with #N/A error, I have made sure the text in the two
different
cells match.

it takes quite a while to find the answer,
is there a quiicker optio[n]?

Well, that's because you need a fairly complex formula to work with that
setup. That's why I showed you the other method.

Does every model have the same number of part descriptions? In your
sample
data each model has 4 descriptions.

--
Biff
Microsoft Excel MVP


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?

:

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


Yes, but the Test & Test 2 are merged cells

:

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


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