lookup multiple criteria

[Alonso]

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

 

[T. Valko]

Try this array formula** :

=INDEX(Sheet1!D1100,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)

 

[Alonso]

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]

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)*(Sheet1!C1:C10=C1)),IF(INDEX(Sheet1!D110,MATCH(1,(Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1),0))="","",INDEX(Sheet1!D110,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))

 

[Alonso]

awesome Biff!!

you have just save my day!!

That's gonna result in a monster formula!

=IF(SUM((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1)),IF(INDEX(Sheet1!D110,MATCH(1,(Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1),0))="","",INDEX(Sheet1!D110,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))

 

[CICRPR]

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.

I’m 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]

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

awesome Biff!!

you have just save my day!!

 

[Ashish Mathur]

Hi,

Try this

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com