XLOOKUP OR IF(AND or something else?

[James8761]

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.

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

The Data Columns I am looking at are;

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

Thanks,
James

 

[hagia_sofia]

Hello, maybe something like:

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

 

[Peter_SSs]

Another possibility to try

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

 

[James8761]

Hello, maybe something like:

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

Thank you very much, works perfectly!