Horizontal lookup, two conditions

[Bilingual]

Hi , i have to columns with numbers(usernumber/day of week) where i want an lookup formula to match these values with similar values in another sheet, and return "1", if found and "0" if not, how do i do this?

Example:

Usernumber Day of week
1 3

 

[T. Valko]

Hi , i have to columns with numbers(usernumber/day of week) where i want an lookup formula to match these values with similar values in another sheet, and return "1", if found and "0" if not, how do i do this?

Example:

Usernumber Day of week
1 3

Try one of these...

This one will work in ALL versions of Excel:

=--(SUMPRODUCT(--(Sheet2!A2:A10=1),--(Sheet2!B2:B10=3))>0)

This one will work in Excel 2007 and later:

=--(COUNTIFS(Sheet2!A2:A10,1,Sheet2!B2:B10,3)>0)

 

[Bilingual]

Try one of these...

This one will work in ALL versions of Excel:

=--(SUMPRODUCT(--(Sheet2!A2:A10=1),--(Sheet2!B2:B10=3))>0)

This one will work in Excel 2007 and later:

=--(COUNTIFS(Sheet2!A2:A10,1,Sheet2!B2:B10,3)>0)

Thanks , is it an arrray formula?

 

[T. Valko]

Thanks , is it an arrray formula?

No, just a normal enter will do.

 

[Bilingual]

No, just a normal enter will do.

Hi again, i just need to understand the formula and where i should direct it.

If Sheet 1 is the main sheet where the look up results should be inserted and Sheet 2 is where the value is situated, how should i set it up?

 

[T. Valko]

Try one of these...

This one will work in ALL versions of Excel:

=--(SUMPRODUCT(--(Sheet2!A2:A10=1),--(Sheet2!B2:B10=3))>0)

This one will work in Excel 2007 and later:

=--(COUNTIFS(Sheet2!A2:A10,1,Sheet2!B2:B10,3)>0)

Hi again, i just need to understand the formula and where i should direct it.

If Sheet 1 is the main sheet where the look up results should be inserted and Sheet 2 is where the value is situated, how should i set it up?

I have the formulas set to look on Sheet2 for matches.

So, I guess you want to enter the formulas on your Sheet1.

What you could do is replace the hardcoded lookup values with cell references. For example:

Sheet1:

A2 = 1
B2 = 3

Then you probably want to enter one of these formulas in cell C2:

=--(SUMPRODUCT(--(Sheet2!A2:A10=A2),--(Sheet2!B2:B10=B2))>0)

=--(COUNTIFS(Sheet2!A2:A10,A2,Sheet2!B2:B10,B2)>0)

 

[Bilingual]

Thanks a lot, it worked fine!!!

 

[T. Valko]

Thanks a lot, it worked fine!!!

You're welcome. Thanks for the feedback!