Horizontal lookup, two conditions

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,549
Messages
5,596,794
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top