XLOOKUP OR IF(AND or something else?

James8761

Board Regular
Joined
Apr 24, 2012
Messages
163
Office Version
  1. 365
Platform
  1. Windows
Hi,
I think I am complicating things but I've been trying for a while, so time to ask for help....

I have two tabs on a workbook. Data and Lookups. On the Lookups tab I am trying to look at if an employee was working in Jan 24, Feb 24 etc, then VLOOKUP a table and return a value.

1741099201806.png


Answers from my current formula are in Cells Q34:V115.
The correct answers are in Cells Q119:V120 (typed in).



The formula in Cell Q34 is
1741099224465.png



The Data Columns I am looking at are;
1741099338268.png



Does anyone have any idea how i can get this to work please?

Thanks,
James
 

Attachments

  • 1741099138699.png
    1741099138699.png
    5.3 KB · Views: 5
Hello, maybe something like:

Excel Formula:
=IF(SUM((Data!$S$2:$S$1000=$P34)*(Data!$P$2:$P$1000=Q$33)),XLOOKUP(Q$33,$P$8:$P$13,$Q$8:$Q$13),0)
 
Upvote 0
Solution
Another possibility to try
Excel Formula:
=VLOOKUP(Q$33,$P$8:$Q$31,2,0)*(COUNTIFS(Data!$P$1:$P$1000,Q$33,Data!$S$1:$S$1000,$P34)>0)

If you still need help please give us some small sample data from each sheet with XL2BB
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

We would need to know more about the 'date' cells:
  • Are they real dates (numbers) or are they text?
  • If real dates, are they the first day of the month, last day of the month or any day in the month?
 
Upvote 0

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
Back
Top