Lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 different product lines. Each product line can produce upto 40 different items on any given day. I already have a list of produced items and in a different column I have a number "1 or 2" which associates the line number to the item produced. On a different sheet I am separating each item to its respective line numbers. The problem I'm having is that my VLOOKUP is searching for a "2" and when it is found then it returns a specific value, but on the next row it returns the same value and I need it to return the value of the next "2

exampl

1 5" x 8
1 7" x 7
2 8" x 3
2 13" x 9
1 6" x 3
1 5" x 4
2 7" x 8

Value wanting to be returne

8" x 3
13" x 9
7" x 8
 
Suggest using data>filter>autofilter

--
Don Guillett
SalesAid Software
(e-mail address removed)
Troy said:
I have 2 different product lines. Each product line can produce upto 40
Click to expand...
different items on any given day. I already have a list of produced items
and in a different column I have a number "1 or 2" which associates the line
number to the item produced. On a different sheet I am separating each item
to its respective line numbers. The problem I'm having is that my VLOOKUP is
searching for a "2" and when it is found then it returns a specific value,
but on the next row it returns the same value and I need it to return the
value of the next "2"
 
Not sure if that will work, but Macros are not allowed on our server due to having to change the security level.
 
Troy,

I have the same problem but in a different application.
Although I am looking for a cleaner method I am currently
using multiple if statements resulting in a potential
array of data exactly the same length as the original
data array. ie, i'm doing it the 'manual way'. (BAD WAY)

Eg using your example :

1 5" x 8"
1 7" x 7"
2 8" x 3"
2 13" x 9"
1 6" x 3"
1 5" x 4"
2 7" x 8"

Output display as (example output displayed into R10C10):

First entry in output display has a formula
if r1c1 = 2 then cell becomes = r1c2 else if r2c1 =2
then... etc

Second entry in the output display has the exact same
formula but with an additional logical check

ie
if r1c1 =2 and r10c10 <> r1c2 then cell becomes = r1c1
else if r2c1 =2 and r10c10 <> r1c2 then ... etc


And so on...

Note that this method is very tedious if you have lots of
data.

I'll post if I find another solution....
-----Original Message-----
I have 2 different product lines. Each product line can
Click to expand...
produce upto 40 different items on any given day. I
already have a list of produced items and in a different
column I have a number "1 or 2" which associates the line
number to the item produced. On a different sheet I am
separating each item to its respective line numbers. The
problem I'm having is that my VLOOKUP is searching for
a "2" and when it is found then it returns a specific
value, but on the next row it returns the same value and
I need it to return the value of the next "2"
 
How can it be "automatic", as desired, without a macro?
I guess a myriad of choose formulas could work.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Troy said:
Not sure if that will work, but Macros are not allowed on our server due
Click to expand...
to having to change the security level.
 
I too had a similar problem. Two types of products, with different costs. Needed to analyze costs of different combinations of products. Luckily I only needed to worry about a max of 50 products. At the top of the sheet I had a 50 row matrix that had all my cost and other data. One of the columns was a Type Identifier (in my case F or E, in yours, 1 or 2). Below the source data, I had two identically structured matrices. In each cell was: "=IF($B8="f",D8,)" in the first matrix and "=IF($B8="e",D8,)" in the second. I then summed certain columns in these derivative matrices and multiplied the results by various costs constants elsewhere in the workbook

Assuming you don't have too much data, you should be able to do something similar, maybe using COUNTIF on the resulting matrices.
 
See if this old post addresses your question:

http://tinyurl.com/2x8k5
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Not sure if that will work, but Macros are not allowed on our server due to
having to change the security level.
 
Back
Top