| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If or Lookup or Something else
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If or Lookup or Something else
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If or Lookup or Something else
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If or Lookup or Something else
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If or Lookup or Something else
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If or Lookup or Something else
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 "KimC" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If or Lookup or Something else
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. "T. Valko" wrote: 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 "KimC" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If or Lookup or Something else
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 "KimC" wrote in message ... 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. "T. Valko" wrote: 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 "KimC" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If or Lookup or Something else
Eh...
Disregard that last formula. After some testing it doesn't work properly. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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 "KimC" wrote in message ... 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. "T. Valko" wrote: 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 "KimC" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
| Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Linear Mode
