XLOOKUP Error

[senthil murugn]

Hi,

I have three columns namely Date (Col A), Time (Col B), Description (Col C) in sheet1.
On Sheet2 trying to list out the description column based on date & time column.
IT is working only for few row and for other returns N/A.

Could you please help me out.

Thanks
Senthil

 

[p45cal]

try in row 2:

=XLOOKUP($B$7+C2,Sheet1!$A$2:$A$23+Sheet1!$B$2:$B$23,Sheet1!$C$2:$C$23)

copy down.

Or if you have O365 in a single cell:

=XLOOKUP(B7+C2:C23,Sheet1!A2:A23+Sheet1!B2:B23,Sheet1!C2:C23)

which should spill down.

 

[senthil murugn]

Hi,

Tried with first one still gives #N/A for all rows, previously it gave randomly some row
with expected values, when tried with if match found = 0, results in 0.

Second is giving #Spill down & not giving the correct result.

Thanks
Senthil M

 

[p45cal]

1. Have you got the date right in cell B7? All the dates on Sheet1 are 7 May:




Regarding the Spill error, make sure the 21 cells below the single cell formula are clear:

 

[senthil murugn]

Hi,

Terribly sorry for my mistake, cell B7 contains today's date which was file creation date.
But the description (Sheet1) list's were not reflecting the same, Once fixed the B7 everything seems to be right now.
Thank for your corrections.

Thanks you
Senthil M.