Hi

I have an array with a lot of data on Sheet1
Column A list styles
Column B list product
Column C list color code
Column D list color name

on Sheet2, I have dropdown menus
on Col A, you select the style
on Col B, you click on the product
and in Col C, you select the color code

I want to display in Col D the color name

Since I have a lot of color codes for each product
and a lot of products for each style
a VLOOKUP won't work

how can I search for the color names??

Try this array formula** :

=INDEX(Sheet1!D1:D100,MATCH(1,(Sheet1!A1:A100=A1)* (Sheet1!B1:B100=B1)*(Sheet1!C1:C100=C1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Hi

I have an array with a lot of data on Sheet1
Column A list styles
Column B list product
Column C list color code
Column D list color name

on Sheet2, I have dropdown menus
on Col A, you select the style
on Col B, you click on the product
and in Col C, you select the color code

I want to display in Col D the color name

Since I have a lot of color codes for each product
and a lot of products for each style
a VLOOKUP won't work

how can I search for the color names??

Like a charm Biff
thanks!!

just another question
if Col C is empty, i get a zero if col B or A is empty too
and #N/A if they have values

can i get rid of those??



"T. Valko" wrote:

Try this array formula** :

=INDEX(Sheet1!D1:D100,MATCH(1,(Sheet1!A1:A100=A1)* (Sheet1!B1:B100=B1)*(Sheet1!C1:C100=C1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Hi

I have an array with a lot of data on Sheet1
Column A list styles
Column B list product
Column C list color code
Column D list color name

on Sheet2, I have dropdown menus
on Col A, you select the style
on Col B, you click on the product
and in Col C, you select the color code

I want to display in Col D the color name

Since I have a lot of color codes for each product
and a lot of products for each style
a VLOOKUP won't work

how can I search for the color names??

if Col C is empty, i get a zero if col B or A is
empty too and #N/A if they have values
can i get rid of those??

That's gonna result in a monster formula!

=IF(SUM((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(She et1!C1:C10=C1)),IF(INDEX(Sheet1!D1:D10,MATCH(1,(Sh eet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10= C1),0))="","",INDEX(Sheet1!D1:D10,MATCH(1,(Sheet1! A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1),0 ))),"")

You might just want to use the original formula and then use another smaller
formula in a different cell like this:

Assume the original formula is in cell X1. Then:

=IF(ISNA(X1),"",IF(X1=0,"",X1))


--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Like a charm Biff
thanks!!

just another question
if Col C is empty, i get a zero if col B or A is empty too
and #N/A if they have values

can i get rid of those??



"T. Valko" wrote:

Try this array formula** :

=INDEX(Sheet1!D1:D100,MATCH(1,(Sheet1!A1:A100=A1)* (Sheet1!B1:B100=B1)*(Sheet1!C1:C100=C1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Hi

I have an array with a lot of data on Sheet1
Column A list styles
Column B list product
Column C list color code
Column D list color name

on Sheet2, I have dropdown menus
on Col A, you select the style
on Col B, you click on the product
and in Col C, you select the color code

I want to display in Col D the color name

Since I have a lot of color codes for each product
and a lot of products for each style
a VLOOKUP won't work

how can I search for the color names??

awesome Biff!!

you have just save my day!!


"T. Valko" wrote:

if Col C is empty, i get a zero if col B or A is
empty too and #N/A if they have values
can i get rid of those??

That's gonna result in a monster formula!

=IF(SUM((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(She et1!C1:C10=C1)),IF(INDEX(Sheet1!D1:D10,MATCH(1,(Sh eet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10= C1),0))="","",INDEX(Sheet1!D1:D10,MATCH(1,(Sheet1! A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1),0 ))),"")

You might just want to use the original formula and then use another smaller
formula in a different cell like this:

Assume the original formula is in cell X1. Then:

=IF(ISNA(X1),"",IF(X1=0,"",X1))


--
Biff
Microsoft Excel MVP

I have been unsuccessful figuring out the formula. I am trying to set column
A with a fixed value number (hours), but this column cannot exceed the
working hours in column B. The fixed value will depend on the total amount
of specimens. The hours in column A are default by the following
information: 20 specimens = fixed value of 2.0 = working hrs of 2.5 ; 30
speicmens = fixed value of 2.5 = working hrs of 3.0.

Im currently using this:

=LOOKUP(D11,{20,30,40,50,60,70,80,90,100},{"2","2. 5","3.5","4.0","5.0","6.0","6.5","7.5","8.0"})

But€¦..now I might have 25, 66, 77, etc. specimens and thus the fixed value
should be higher than the standard value and working hours will increase.

In the past I have try the conditional formula €œIF€�. Eg.
If(k=<20,€�2.0€�;<20,€�2.5€�€¦etc)

Does anyone have any ideas as to solving this issue?



"T. Valko" wrote:

Try this array formula** :

=INDEX(Sheet1!D1:D100,MATCH(1,(Sheet1!A1:A100=A1)* (Sheet1!B1:B100=B1)*(Sheet1!C1:C100=C1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Hi

I have an array with a lot of data on Sheet1
Column A list styles
Column B list product
Column C list color code
Column D list color name

on Sheet2, I have dropdown menus
on Col A, you select the style
on Col B, you click on the product
and in Col C, you select the color code

I want to display in Col D the color name

Since I have a lot of color codes for each product
and a lot of products for each style
a VLOOKUP won't work

how can I search for the color names??

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
awesome Biff!!

you have just save my day!!


"T. Valko" wrote:

if Col C is empty, i get a zero if col B or A is
empty too and #N/A if they have values
can i get rid of those??

That's gonna result in a monster formula!

=IF(SUM((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(She et1!C1:C10=C1)),IF(INDEX(Sheet1!D1:D10,MATCH(1,(Sh eet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10= C1),0))="","",INDEX(Sheet1!D1:D10,MATCH(1,(Sheet1! A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1),0 ))),"")

You might just want to use the original formula and then use another
smaller
formula in a different cell like this:

Assume the original formula is in cell X1. Then:

=IF(ISNA(X1),"",IF(X1=0,"",X1))


--
Biff
Microsoft Excel MVP

Hi,

Try this

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Alonso" wrote in message
...
Hi

I have an array with a lot of data on Sheet1
Column A list styles
Column B list product
Column C list color code
Column D list color name

on Sheet2, I have dropdown menus
on Col A, you select the style
on Col B, you click on the product
and in Col C, you select the color code

I want to display in Col D the color name

Since I have a lot of color codes for each product
and a lot of products for each style
a VLOOKUP won't work

how can I search for the color names??