lookup multiple criteria


A

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

Advertisements

T

T. Valko

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)
 
A

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

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!D1:D10,MATCH(1,(Sheet1!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))
 
A

Alonso

awesome Biff!!

you have just save my day!!


T. Valko said:
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!D1:D10,MATCH(1,(Sheet1!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))
 
C

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

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top